How do I export data using PostgreSQL COPY command in required format?

Question

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?

Answer 1

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.

• khajlk: I am puzzled by this error "ERROR: function array_to_string(text, unknown) does not exist. You might need to add explicit type casts". I get this error due to last line which is array_to_string(array_column,','). I am calling this array from a table where values are stored as text. Do I have to first convert them to array by unnest??
• András Váczi: I was confused by the column name, thinking it was a text[] as opposed to text. Really, fix your column/data types ;)
• khajlk: hehe I will :) but for now problem is resolved. Thanks!