Re: BUG #6155: literal definition of arrays with double qoutes leads to error

Ok, thanks! I found out this issue a three years ago and then there isn't
such explanation... (all this time I've used some workaround) my mistake,
that I didn't look at the new manual before I reported this issue =(

My apologies for wasting your time =(

Best Regards, Alex

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, August 09, 2011 12:02 PM
To: listar
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: [BUGS] BUG #6155: literal definition of arrays with double
qoutes leads to error

"listar" <listar(at)mail(dot)ru> writes:
> SELECT ('{string "with" double quotes}'::text[])[1] as value;
> gives:

> ERROR: malformed array literal: "{string "with" double quotes}"
> LINE 1: SELECT ('{string "with" double quotes}'::text[])[1] as value...
> ^

This is not a bug; that value does not follow the documented rules for array
literals. Per the manual:

As shown previously, when writing an array value you can use double quotes
around any individual array element. You must do so if the element value
would otherwise confuse the array-value parser. For example, elements
containing curly braces, commas (or the data type's delimiter character),
double quotes, backslashes, or leading or trailing whitespace must be
double-quoted. Empty strings and strings matching the word NULL must be
quoted, too. To put a double quote or backslash in a quoted array element
value, use escape string syntax and precede it with a backslash.
Alternatively, you can avoid quotes and use backslash-escaping to protect
all data characters that would otherwise be taken as array syntax.

An example of correct format is

regression=# select E'{"string \\"with\\" double quotes"}'::text[];
text
-----------------------------------
{"string \"with\" double quotes"}
(1 row)

Frequently it's easier to use an array[] constructor:

regression=# select array['string "with" double quotes'::text];
array
-----------------------------------
{"string \"with\" double quotes"}
(1 row)

regards, tom lane