Обсуждение: 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