postgresql copy from with csv array literal whose delimiter is ','

Question

I'd like to copy from with csv file with Postgres.

That csv file has array literal whose delimiter is ;.

example: a,b,c,{1;2;3}

I did that with replacing delimiter of csv file , to | and set option delimiter | and replacing delimiter of array literal ; to ,.

example: a|b|c|{1,2,3}

I think there may be option to set delimiter of array literal.
If so, I don't have to replace delimiter of csv file.

Are there any smarter way?

Check this post How to import CSV file data into a PostgreSQL table? — cabesuon
– cabesuon, Commented Jan 27, 2020 at 1:57

Laurenz Albe · Accepted Answer · 2020-01-27 07:33:33Z

1

There is no option to configure the separator between the elements of an array in its text representation.

But if you have any control over how the CSV file is generated, you can escape the array literal:

a,b,c,"{1,2,3}"

That would work fine with COPY.

answered Jan 27, 2020 at 7:33

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1