Re: escaping double-quotes in varchar array

On Tue, Nov 8, 2022 at 6:27 PM David Gauthier <dfgpostgres(at)gmail(dot)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.