PostgreSQL – How to Export Data Using COPY Command

postgresql

I have some sample data in my PostgreSQL 9.5 db like follows:

ID(integer)   value_1(numeric)   value_2(numeric)  array(text)
1             25.2               14.1              1,112,292,19.7
2             11.3               5.9               2,30,110,22.3,60,270,30.1

I am exporting this data in my_table using Postgres COPY command to a custom text file like this:

Copy
(
Select
ID, value_1,
value_2, array
from
my_table
Order by ID
) to '~my_path/output.str' With DELIMITER ',';

I get the exported output like:

1,25.2,14.1,1\,112\,292\,19.7\
2,11.3,5.9,2\,30\,110\,22.3\,60\,270\,30.1\

However, my desired output is:

1,25.2,14.1,1,112,292,19.7
2,11.3,5.9,2,30,110,22.3,60,270,30.1

How do I remove these unwanted characters \ in COPY command export output?

Best Answer

You cannot. Just ask an editor to replace \, with ,.

Otherwise, if the desired output format is really what you want, you can change the query:

SELECT id::text || ',' || 
       value_1::text || ',' || ... || 
       your_array_column
...

In any case, I'd suggest fixing your array data type. Storing numbers as text looks really bad.

Related Question