Inserting string with a " into an array using {} syntax ... possible?

From: Bill Moran <wmoran(at)potentialtech(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Inserting string with a " into an array using {} syntax ... possible?
Date: 2009-03-13 13:21:10
Message-ID: 20090313092110.da3178be.wmoran@potentialtech.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavel Stehule 2009-03-13 13:41:43 Re: Inserting string with a " into an array using {} syntax ... possible?
Previous Message emsa 2009-03-13 13:10:12 postmaster hangs on delete from