On Tue, Nov 8, 2022 at 6:27 PM David Gauthier <dfgpostgres@xxxxxxxxx> 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.
