Thread: 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