Re: array syntax and geometric type syntax

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/