PostgreSQL Copy To - CSV filename encoding

Question

I have a database setup with a UTF-8 encoding. Trying to copy a table to csv, where the filename has a special character writes out the filename wrong to disk.

On a Windows 10 localhost PostgreSQL installation:

copy
  (select 'tønder')
to 'C:\temp\Sønderborg.csv' (FORMAT CSV, HEADER TRUE, DELIMITER ';', ENCODING 'UTF8');

Names the csv file: SÃ¸nderborg.csv and not Sønderborg.csv.

Both

SHOW CLIENT ENCODING;
SHOW SERVER_ENCODING;

returns UTF8

How can one control the csv filename encoding? The encoding inside the csv is ok writing Tønder!

UPDATE

I have run the copy command from pgAdmin, DataGrip and a psql console. DataGrip uses JDBC and will only handle UTF8. All three applications writes the csv filename in wrong encoding. The only difference is that the psql console says the client encoding is WIN1252.

I updated the question with program running the copy command — Jakob
– Jakob, Commented Mar 5, 2019 at 14:45

Nick Barnes · Accepted Answer · 2019-03-06 11:20:40Z

5

I don't think it's possible to change this behaviour. It looks like Postgres assumes that the filename encoding matches the server_encoding (as suggested on the mailing lists here and here). The only workaround I could find was to run the command while connected to a WIN1252-encoded database, which is probably not very helpful.

If you're trying to run this on the same machine as the server itself, then instead of using the server-side COPY, you can run psql's client-side \copy, which will respect your client_encoding when interpreting the file path:

psql -c "\copy (select 'tønder') to 'C:\temp\Sønderborg.csv' (FORMAT CSV, HEADER TRUE, DELIMITER ';', ENCODING 'UTF8')"

Note that cmd.exe (and even powershell.exe) still uses legacy DOS encodings by default, so you might need to run chcp 1252 to set the console codepage before launching psql.

answered Mar 6, 2019 at 11:20

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

1 Comment

Jakob Over a year ago

Thanks for the clarification, the psql \copy returns the correct filename.

Collectives™ on Stack Overflow

PostgreSQL Copy To - CSV filename encoding

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related