escaping double-quotes in varchar array

From: David Gauthier <dfgpostgres(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: escaping double-quotes in varchar array
Date: 2022-11-09 01:27:35
Message-ID: CAEs=6DnhWbh5QfbZAZy6BdoGdLJ28MCoU1T6pAiZhw6Ze4gMUw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

psql (11.5, server 11.3) on linux
Trying to insert a string containing a double-quote into a varchar arr.
Here's my attempt

dvdb=# create table testarr (arr varchar[]);
CREATE TABLE
dvdb=# insert into testarr (arr) values ('{"abcdefg"}');
INSERT 0 1
dvdb=# select * from testarr;
arr
-----------
{abcdefg}
(1 row)

dvdb=# insert into testarr (arr) values ('{"hijk"lmnop"}');
ERROR: malformed array literal: "{"hijk"lmnop"}"
LINE 1: insert into testarr (arr) values ('{"hijk"lmnop"}');
^
DETAIL: Unexpected array element.
dvdb=# insert into testarr (arr) values ('{"hijk""lmnop"}');
ERROR: malformed array literal: "{"hijk""lmnop"}"
LINE 1: insert into testarr (arr) values ('{"hijk""lmnop"}');
^
DETAIL: Unexpected array element.
dvdb=# insert into testarr (arr) values ('{"hijk\"lmnop"}');
INSERT 0 1
dvdb=# select * from testarr;
arr
-----------------
{abcdefg}
{"hijk\"lmnop"}
(2 rows)

What I'm looking for is...

{abcdefg}
{hijk"lmnop}

Can this be done ?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-11-09 01:38:19 Re: escaping double-quotes in varchar array
Previous Message Adrian Klaver 2022-11-09 01:02:31 Re: "set role" semantics