BUG #6155: literal definition of arrays with double qoutes leads to error
The following bug has been logged online:
Bug reference: 6155
Logged by: listar
Email address: listar@mail.ru
PostgreSQL version: 8.4.5
Operating system: Gentoo 4.4.4-r2 p1.3, pie-0.4.5 64-bit
Description: literal definition of arrays with double qoutes leads to
error
Details:
for example:
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...
^
********** Error **********
ERROR: malformed array literal: "{string "with" double quotes}"
SQL state: 22P02
Character: 9
Expected result:
value
---------------------------
string "with" double quotes
(1 row)
I tried to escape input string in different ways, but it wasn't successful
=(
as I can understood the problem is in "switch" statement in method
"ArrayCount(...)" in file \src\backend\utils\adt\arrayfuncs.c
(that switch doesn't cover all possible cases)
"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
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