array syntax and geometric type syntax

From: "Dan Halbert" <halbert(at)halwitz(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: array syntax and geometric type syntax
Date: 2009-08-13 15:02:37
Message-ID: 1250175757.106114814@192.168.1.35
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Durumdara 2009-08-13 15:20:22 Can I get Field informations from system tables?
Previous Message Alvaro Herrera 2009-08-13 14:59:27 Re: totally different plan when using partitions