Encoding in SQL to CSV

Question

When do I execute query COPY TO ... CSV, I create CSV file. BUt when open it column with names in excel that should be with national characters are not as it should be. So my question is, If it is possible within a sql query to change this encoding to utf8? Or something else? Because I want that new created CSV file to be as final product for user on web. I hope someone understood what I want:)

Answer 1

Postgresql doesn't seem to support transcoding column names when copying as csv. I think this is what you're asking for? However, you can structure the "copy" command to rename the columns as required.

So for example, if I create a table so:

steve@steve@[local] =# copy csvtest to stdout with csv header;
id,value
1,Telewizja Polska zawiesiła dzisiejszą emisję programu 

You can have it transcode the data to something else:

steve@steve@[local] =# set client_encoding = 'iso8859-2';
SET
steve@steve@[local] =# copy csvtest to stdout with csv header;
id,value
1,Telewizja Polska zawiesi�a dzisiejsz� emisj� programu 

But if you define column names in a national characters:

steve@steve@[local] =# reset client_encoding ;
RESET
steve@steve@[local] =# copy (select id as id, value as "emisję" from csvtest) to stdout with csv header;
id,emisję
1,Telewizja Polska zawiesiła dzisiejszą emisję programu 

Then these aren't transcoded:

steve@steve@[local] =# set client_encoding = 'iso8859-2';
SET
steve@steve@[local] =# copy (select id as id, value as "emisję" from csvtest) to stdout with csv header;
id,emisję
1,Telewizja Polska zawiesi�a dzisiejsz� emisj� programu 

Presumably the UTF-8 I'm sending as part of the COPY command is just being echoed back to me without being decoded.