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/