Re: Differences in Unicode handling on Mac vs Linux?

2013/6/3 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Matt Daw <matt(at)shotgunsoftware(dot)com> writes:
>> Howdy, I loaded a client's DB on my Mac to debug an unrelated bug, but
>> I'm blocked because my Mac is rejecting SQL that works on our Linux
>> production servers. Here's a simple case:
>
>> # select * from shots where sg_poznÁmka is NULL;
>> ERROR: column "sg_pozn�mka" does not exist
>> LINE 1: select * from shots where sg_poznÁmka is NULL;
>
> Hm ... what does "\d shots" say about the spelling of the column name?
>
>> Anything else I could double-check? Or are there any known Mac-related
>> Unicode issues?
>
> OS X's Unicode locales are pretty crummy. I'm suspicious that there's
> some sort of case-folding inconsistency here, but it's hard to say more
> (especially since you didn't actually tell us *which* locales you've
> selected on each machine). If it is that, as a short-term fix it might
> help to double-quote the column name.

I can recreate something similar (OS X 10.7, 9.3beta1):

postgres=# CREATE TABLE shots (id int);
CREATE TABLE
postgres=# SHOW client_encoding ;
client_encoding
-----------------
UTF8
(1 row)

postgres=# select * from shots where col_ä is NULL;
ERROR: column "col_�" does not exist
LINE 1: select * from shots where col_ä is NULL;

The corresponding log output is:

ERROR: column "col_<E3><A4>" does not exist at character 27
STATEMENT: select * from shots where col_ä is NULL;

Double-quoting the column name does seem to "work":

postgres=# select * from shots where "col_ä" is NULL;
ERROR: column "col_ä" does not exist
LINE 1: select * from shots where "col_ä" is NULL;

The only language/locale settings I see in my environment are:

LANG=en_GB.UTF-8
__CF_USER_TEXT_ENCODING=0x1F6:0:2

Regards

Ian Barwick