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@sss.pgh.pa.us]
Sent: Tuesday, August 09, 2011 12:02 PM
To: listar
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #6155: literal definition of arrays with double
qoutes leads to error
"listar" <listar@mail.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