Re: escaping double-quotes in varchar array

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: escaping double-quotes in varchar array
Дата
Msg-id CAKFQuwaP4qgLcfPJqKqkdkz3-35Mtw_bczhFwz23BsF_CfoLuQ@mail.gmail.com
обсуждение исходный текст
Ответ на escaping double-quotes in varchar array  (David Gauthier <dfgpostgres@gmail.com>)
Список pgsql-general
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.

В списке pgsql-general по дате отправления:

Предыдущее
От: David Gauthier
Дата:
Сообщение: escaping double-quotes in varchar array
Следующее
От: Michael Paquier
Дата:
Сообщение: Re: Q: pg_hba.conf separate database names file format