I am trying to make sense of geometric literal syntax in and out of array syntax. I cannot figure out a general rule: sometimes single quotes work, sometimes double quotes work, and inside and outside of array literals the rules are different an seemingly inconsistent.
Examples of all the weird cases are below. If someone could explain what the SQL parser is really looking for, and what the "best" or "most correct" way is, I would be grateful.
Thanks,
Dan
------------------------
Examples:
db=# create temporary table x (p point);
CREATE TABLE
Can't use bare point notation:
db=# insert into x values ( (1,2) );
ERROR: column "p" is of type point but expression is of type record
HINT: You will need to rewrite or cast the expression.
Can use single-quoted points:
db=# insert into x values ( '(1,2)' );
INSERT 0 1
Can't use double-quoted points:
db=# insert into x values ( "(1,2)" );
ERROR: column "(1,2)" does not exist
LINE 1: insert into x values ( "(1,2)" );
Function notation works, as expected:
db=# insert into x values (point(1,2));
INSERT 0 1
Casting works, as expected:
db=# insert into x values ( '(1,2)'::point );
INSERT 0 1
Values print without quotes:
db=# select * from x;
p
-------
(1,2)
(1,2)
(1,2)
(3 rows)
OK, now try an array of points:
db=# create temporary table y (pa point[]);
CREATE TABLE
ARRAY[] with single quoted value doesn't work:
db=# insert into y values (array[ '(1,2)' ]);
ERROR: column "pa" is of type point[] but expression is of type text[]
HINT: You will need to rewrite or cast the expression.
ARRAY[] with double quoted value doesn't work:
db=# insert into y values (array [ "(1,2)" ]);
ERROR: column "(1,2)" does not exist
LINE 1: insert into y values (array [ "(1,2)" ]);
^
Array[] with casting a quoted string works:
db=# insert into y values (array [ '(1,2)'::point ]);
INSERT 0 1
ARRAY[] with point() works:
db=# insert into y values (array [ point(1,2) ]);
INSERT 0 1
{} notation with unquoted value inside doesn't work:
db=# insert into y values ('{ (1,2) }');
ERROR: invalid input syntax for type point: "(1"
{} notation with double quotes inside works!!:
db=# insert into y values ('{ "(1,2)" }');
INSERT 0 1
{} with cast doesn't work:
db=# insert into y values ( '{ ''(2,3)''::point }');
ERROR: invalid input syntax for type point: "'(2"
{} with point() doesn't work:
db=# insert into y values ( '{ point(2,3) }');
ERROR: invalid input syntax for type point: "point(2"
Values print with {} and double-quote notation inside:
db=# select * from y;
pa
-----------
{"(1,2)"}
{"(1,2)"}
{"(1,2)"}
(3 rows)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
