Thread: Inserting string with a " into an array using {} syntax ... possible?
Between google searches and my own experiments, I can't find any way to
actually make this work.
I have a TEXT[] column, and one of the values I want to insert is
'text "for" you'.
Is there no way to do this using the {} syntax? Chronicled below are
several of my attempts (various noise has been snipped):
bill=# create table testarray (a TEXT[], id BIGSERIAL PRIMARY KEY);
bill=# insert into testarray (a) values (E'{"text \"for you\"","moretext"}');
ERROR: malformed array literal: "{"text "for you"","moretext"}"
bill=# insert into testarray (a) values (E'{"text \\"for you\\"","moretext"}');
INSERT 0 1
bill=# select * from testarray;
a | id
-------------------------------+----
{"text \"for you\"",moretext} | 3
bill=# insert into testarray (a) values (E'{"text \"for you\"","moretext"}');
ERROR: malformed array literal: "{"text "for you"","moretext"}"
bill=# insert into testarray (a) values (E'{"text ""for you","moretext"}');
ERROR: malformed array literal: "{"text ""for you","moretext"}"
bill=# insert into testarray (a) values (E'{"text "for" you","moretext"}');
ERROR: malformed array literal: "{"text "for" you","moretext"}"
bill=# insert into testarray (a) values (E'{"text \"for\" you","moretext"}');
ERROR: malformed array literal: "{"text "for" you","moretext"}"
bill=# insert into testarray (a) values (E'{"text \042for\042 you","moretext"}');
ERROR: malformed array literal: "{"text "for" you","moretext"}"
bill=# insert into testarray (a) values (E'{"text \\042for\\042 you","moretext"}');
INSERT 0 1
bill=# select * from testarray;
a | id
---------------------------------+----
{"text 042for042 you",moretext} | 4
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
Hello
try
postgres=# SELECT ARRAY['text "for you" some'];
array
---------------------------
{"text \"for you\" some"}
(1 row)
regards
Pavel Stehule
2009/3/13 Bill Moran <wmoran@potentialtech.com>:
>
> Between google searches and my own experiments, I can't find any way to
> actually make this work.
>
> I have a TEXT[] column, and one of the values I want to insert is
> 'text "for" you'.
>
> Is there no way to do this using the {} syntax? Chronicled below are
> several of my attempts (various noise has been snipped):
>
> bill=# create table testarray (a TEXT[], id BIGSERIAL PRIMARY KEY);
>
> bill=# insert into testarray (a) values (E'{"text \"for you\"","moretext"}');
> ERROR: malformed array literal: "{"text "for you"","moretext"}"
>
> bill=# insert into testarray (a) values (E'{"text \\"for you\\"","moretext"}');
> INSERT 0 1
> bill=# select * from testarray;
> a | id
> -------------------------------+----
> {"text \"for you\"",moretext} | 3
>
> bill=# insert into testarray (a) values (E'{"text \"for you\"","moretext"}');
> ERROR: malformed array literal: "{"text "for you"","moretext"}"
>
> bill=# insert into testarray (a) values (E'{"text ""for you","moretext"}');
> ERROR: malformed array literal: "{"text ""for you","moretext"}"
> bill=# insert into testarray (a) values (E'{"text "for" you","moretext"}');
> ERROR: malformed array literal: "{"text "for" you","moretext"}"
>
> bill=# insert into testarray (a) values (E'{"text \"for\" you","moretext"}');
> ERROR: malformed array literal: "{"text "for" you","moretext"}"
>
> bill=# insert into testarray (a) values (E'{"text \042for\042 you","moretext"}');
> ERROR: malformed array literal: "{"text "for" you","moretext"}"
>
> bill=# insert into testarray (a) values (E'{"text \\042for\\042 you","moretext"}');
> INSERT 0 1
> bill=# select * from testarray;
> a | id
> ---------------------------------+----
> {"text 042for042 you",moretext} | 4
>
>
> --
> Bill Moran
> http://www.potentialtech.com
> http://people.collaborativefusion.com/~wmoran/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
In response to Pavel Stehule <pavel.stehule@gmail.com>:
> Hello
>
> try
> postgres=# SELECT ARRAY['text "for you" some'];
> array
> ---------------------------
> {"text \"for you\" some"}
> (1 row)
Thanks, and I'm aware of that, but it doesn't answer the original
question. The code I'm writing is back-end code that other developers
will use to submit queries.
I don't have the luxury of changing their queries from {} syntax to
[] syntax at runtime. However, if this is something that can't be
done with {}, I can catch it during data validation and throw a useful
error.
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
Bill Moran wrote:
> bill=# insert into testarray (a) values (E'{"text \\"for you\\"","moretext"}');
> INSERT 0 1
> bill=# select * from testarray;
> a | id
> -------------------------------+----
> {"text \"for you\"",moretext} | 3
Actually this one is good. It gets the backslashes in the output because the "
need to be escaped there too ('cause it's an array). But if you output a
single element, they are not there:
alvherre=# insert into f values (E'{"text \\"for you\\" some"}');
INSERT 0 1
alvherre=# select * from f;
a
---------------------------
{"text \"for you\" some"}
(1 fila)
alvherre=# select a[1] from f;
a
---------------------
text "for you" some
(1 fila)
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
In response to Alvaro Herrera <alvherre@commandprompt.com>:
> Bill Moran wrote:
>
> > bill=# insert into testarray (a) values (E'{"text \\"for you\\"","moretext"}');
> > INSERT 0 1
> > bill=# select * from testarray;
> > a | id
> > -------------------------------+----
> > {"text \"for you\"",moretext} | 3
>
> Actually this one is good. It gets the backslashes in the output because the "
> need to be escaped there too ('cause it's an array). But if you output a
> single element, they are not there:
Bizarre, but it passes my other tests as well, so I'll have to accept
it as correct ... just seems wrong somehow.
Thanks!
--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/
Bill Moran <wmoran@potentialtech.com> writes:
> In response to Alvaro Herrera <alvherre@commandprompt.com>:
>> Actually this one is good. It gets the backslashes in the output because the "
>> need to be escaped there too ('cause it's an array). But if you output a
>> single element, they are not there:
> Bizarre, but it passes my other tests as well, so I'll have to accept
> it as correct ... just seems wrong somehow.
Well, the point is that the array output subroutine re-applies the
escaping, so that what it outputs will be acceptable to array input too
(modulo extra escaping for string literals of course, but dump and
reload via COPY is the driving consideration here). This is all
explained under "Array Input and Output Syntax":
http://www.postgresql.org/docs/8.3/static/arrays.html#AEN6156
regards, tom lane