Thread: array syntax and geometric type syntax
I am trying to make sense of geometric literal syntax in and out of array syntax. I cannot figure out a general rule:
sometimessingle quotes work, sometimes double quotes work, and inside and outside of array literals the rules are
differentan 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)
On Thu, Aug 13, 2009 at 11:02:37AM -0400, Dan Halbert wrote:
> 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.
I'm sure it doesn't look like it, but literal syntax is the same
everywhere. What's confusing is that there's no visual difference
between a text literal any any other type--hence the error messages you
get back from PG are a bit confusing.
Firstly, column references are always at the "top-level" and are always
in double quotes. The other double quotes you were using were "inside"
a literal and hence subject to rules specific to that datatype's literal
input code.
The nicer syntax to distinguish things is to use:
TYPENAME 'literal'
in code. For example:
SELECT INT '1', FLOAT8 '1.1', NUMERIC '1.1';
Points are fun, because although the following look similar and have the
same result:
SELECT POINT '(1,2)', POINT (1,2);
They're actually doing very different things underneath. The first is
a straight point literal, the second is calling the point function and
giving its two parameters. I.e. it's short for:
SELECT POINT (FLOAT '1', FLOAT '2');
Array and Record literals are more complicated because they have to deal
with nesting of things and hence can get quite baroque. Lets go with
arrays first, because that's what your question was about. The basic
literal format is:
'{"elem1","elem2","elem_n"}'
So inside the single quotes, used to indicate a literal, there are
double quotes that separate each element. However all these double
quotes are just a waste of space most of the time, so unless there
are any strange characters (i.e. commas, braces, double quotes or
backslashes if I remember right) it doesn't bother with the double
quotes. For example, if we evaluate the above:
SELECT '{"elem1","elem2","elem_n"}'::TEXT[];
(sadly the normal literal syntax doesn't work for arrays) we get back:
{elem1,elem2,n}
So, PG has read in the literal, turned it into a real value internally,
and then converted it back to a literal to show you the result. These
two conversions have resulted in you getting something different back,
but they both represent the same thing as far as PG is concerned.
--
Sam http://samason.me.uk/
> I am trying to make sense of geometric literal syntax in and out of array syntax. I cannot figure out a general rule: sometimessingle quotes work, sometimes double quotes work, and inside and outside of array literals the rules are differentan seemingly inconsistent. > > Examples of all the weird cases are below. If someone could explain what the SQL parser is really looking for, and whatthe "best" or "most correct" way is, I would be grateful. I'm not sure you have a question here that you didn't answer yourself. Postgres is stricter than most dbms's about typing. It doesn't like to guess about what you probably meant. It's both a blessing and a curse. But you get used to it. You've already gone through what works and what doesn't. Just do what works. :) But to answer the question about why the quotes when it is a point array: Postgres is correctly interpreting and storing your point arrays. But arrays are output as comma separated lists and since your points have embedded commas, it quotes them. Scott
From "Sam Mason" <sam@samason.me.uk>:
>The nicer syntax to distinguish things is to use:
>
> TYPENAME 'literal'
Thanks! That is very helpful. I saw that syntax in one example I found on the web, and incorrectly thought it was an
alternateway of writing the function call.
The point of all this was to figure out a uniform syntax I can use for doing some type adapters to convert back and
forthbetween Python objects and PG objects (through psycopg2 or some other Python-PG interface). Perhaps I should have
mentionedthat initially. I now see that the '{...}' notation does not do any evaluation of what's inside, e.g.
SELECT '{1,2,1+2}'::INT[];
doesn't work, but
SELECT ARRAY[1,2,1+2]::INT[];
works fine.
Dan
On Thu, Aug 13, 2009 at 12:31:29PM -0400, Dan Halbert wrote: > Perhaps I should have mentioned that initially. In retrospect everything is easy! > SELECT ARRAY[1,2,1+2]::INT[]; > works fine. I'd not put a cast into that one. I can't see any performance reason why it's bad, I think it's mainly because it may mask other problems later on. It's not going to affect much fundamental either way though. -- Sam http://samason.me.uk/