Обсуждение: COPY command file name encoding issue (UTF8/WIN1252)

Hi,

I have a problem using COPY command with a file name containing non
ASCII characters.
I use Postgres 9.3.5 x64 on a Windows 7.
OS local encoding is WIN1252.
My database is encoded in UTF8.
I initiate client connection with libpq, connection encoding is set to UTF8.
I build properly my file path taking care of encoding.

When I run COPY "test" TO 'C:/tmp/é.bin' WITH BINARY
it creates a file named é.bin which is utf8 name interpreted as local8.
It could be reproduced convert_from(convert_to('é','UTF8'),'WIN1252').
é in UTF8 "\303\251"
é in WIN1252"\351"

This command works on a database encoded in WIN1252 (same as OS) .
So it seems that COPY command don't take care of file name encoding.
Is it a bug ? a limitation ?
Thanks for your help

Mathieu PUJOL

Pujol Mathieu wrote:
> I have a problem using COPY command with a file name containing non
> ASCII characters.
> I use Postgres 9.3.5 x64 on a Windows 7.
> OS local encoding is WIN1252.
> My database is encoded in UTF8.
> I initiate client connection with libpq, connection encoding is set to UTF8.
> I build properly my file path taking care of encoding.
> 
> When I run COPY "test" TO 'C:/tmp/é.bin' WITH BINARY
> it creates a file named é.bin which is utf8 name interpreted as local8.
> It could be reproduced convert_from(convert_to('é','UTF8'),'WIN1252').
> é in UTF8 "\303\251"
> é in WIN1252"\351"
> 
> This command works on a database encoded in WIN1252 (same as OS) .
> So it seems that COPY command don't take care of file name encoding.
> Is it a bug ? a limitation ?
> Thanks for your help

I didn't look at the code, but I'd say that the database encoding is
used for the file name, which is why it works when database encoding
and OS locale are the same.

I guess that it would be possible for PostgreSQL to figure out with what
OS locale the postmaster is running and to convert file names accordingly,
but it's probably not trivial since it is OS dependent.

Yours,
Laurenz Albe

Maybe a new option could be added to let caller specifies the file name
encoding, it may know it because he create the source/destination file.
I tried to give him a WIN1252 text by doing COPY "test" TO
convert_from(convert_to('C:/tmp/é.bin','UTF8'),'WIN1252') WITH BINARY
but this call is not allowed. Sending him a text containing escaped
WIN1252 hex value fails, because query parser detect invalid UTF8
sequence (which is logical).
The problem is that I can't find any way to workaround this bug.

Regards

Mathieu Pujol

Le 23/03/2015 11:46, Albe Laurenz a écrit :
> Pujol Mathieu wrote:
>> I have a problem using COPY command with a file name containing non
>> ASCII characters.
>> I use Postgres 9.3.5 x64 on a Windows 7.
>> OS local encoding is WIN1252.
>> My database is encoded in UTF8.
>> I initiate client connection with libpq, connection encoding is set to UTF8.
>> I build properly my file path taking care of encoding.
>>
>> When I run COPY "test" TO 'C:/tmp/é.bin' WITH BINARY
>> it creates a file named é.bin which is utf8 name interpreted as local8.
>> It could be reproduced convert_from(convert_to('é','UTF8'),'WIN1252').
>> é in UTF8 "\303\251"
>> é in WIN1252"\351"
>>
>> This command works on a database encoded in WIN1252 (same as OS) .
>> So it seems that COPY command don't take care of file name encoding.
>> Is it a bug ? a limitation ?
>> Thanks for your help
> I didn't look at the code, but I'd say that the database encoding is
> used for the file name, which is why it works when database encoding
> and OS locale are the same.
>
> I guess that it would be possible for PostgreSQL to figure out with what
> OS locale the postmaster is running and to convert file names accordingly,
> but it's probably not trivial since it is OS dependent.
>
> Yours,
> Laurenz Albe
>