PostgreSQL: How do I avoid unwanted characters in COPY command output?

Question

I have an array (text) (sample row) in my PostgreSQL 9.5 database like follows:

my_array(text)
1,112,292,19.7

I am exporting this text array using Postgres COPY command to a custom text file like this:

Copy
(
Select
my_array
from
my_table
Order by my_table_id
) to '~my_path/output.str' With DELIMITER ',';

I get the output:

1\,112\,292\,19.7\

How can I avoid these unwanted \ in my copy command output?

Yeah. It worked! but I have other two columns (numeric) that need to be exported with this array. Thus, I would prefer COMMA as a delimiter. — khajlk
– khajlk, Commented Aug 26, 2017 at 15:05
You could try to add QUOTE "'"of '"' and/or force_quote(my_array) — wildplasser
– wildplasser, Commented Aug 26, 2017 at 15:13
Is this right? With DELIMITER ',' Where FORCE_QUOTE(my_array). Care to explain? — khajlk
– khajlk, Commented Aug 26, 2017 at 15:30

wildplasser · Accepted Answer · 2017-08-26 16:07:49Z

2

if the delimiter character (, in your case) is present in a string, it will be escaped (normally by prefixing it with a \)
If you use a different separator (from ,), the , separator doesn't have to be escaped.
If you quote the string in the output, the , separator doesn't have to be escaped.

-- CREATE TABLE my_table(my_table_id SERIAL PRIMARY KEY, my_array text);
-- INSERT INTO my_table(my_array )VALUES ('1,112,292,19.7') ;

COPY ( SELECT my_array FROM my_table ORDER BY my_table_id)
TO '/tmp/output.str'
WITH CSV DELIMITER ',' QUOTE '"'
        ;

edited Aug 26, 2017 at 16:07

answered Aug 26, 2017 at 15:46

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: How do I avoid unwanted characters in COPY command output?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related