Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "listar" <listar(at)mail(dot)ru>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6155: literal definition of arrays with double qoutes leads to error
Date: 2011-08-09 05:02:20
Message-ID: 6103.1312866140@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

In response to

Responses

pgsql-bugs by date

Next:From: aiDate: 2011-08-09 05:04:15
Subject: Re: BUG #6154: wrong result with nested left-joins
Previous:From: Tom LaneDate: 2011-08-09 04:53:20
Subject: Re: BUG #6154: wrong result with nested left-joins

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group