Thread: escaping double-quotes in varchar array
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)
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}
{hijk"lmnop}
Can this be done ?
On Tue, Nov 8, 2022 at 6:27 PM David Gauthier <dfgpostgres@gmail.com> wrote:
dvdb=# insert into testarr (arr) values ('{"hijk\"lmnop"}');
This is (one of) the correct ways to formulate what you want.
INSERT 0 1
dvdb=# select * from testarr;
arr
-----------------
{abcdefg}
{"hijk\"lmnop"}
And this is the output you will get - because the textual output is going to be round-trippable back into text[]
(2 rows)What I'm looking for is...{abcdefg}
{hijk"lmnop}
You should probably use the array_to_string() function then, to get the contents of the array as un-array-escaped text - you can concatenate the curly brackets onto the output of that function if you like.
Can this be done ?
You are much much better off using array constructor syntax than trying to write an array literal by hand. About the only time you don't have this choice is if your data is CSV. Even then I'd almost rather just import a csv data value and use string_to_array.
For the array constructor you simply write:
ARRAY['hijk"lmnop']::text[]
You still need to deal with the fact that your desired representation of text[] and the default are simply different and thus you need to write a custom expression to produce the output you desire.
David J.