2013/6/3 Tom Lane <tgl@sss.pgh.pa.us>:
> Matt Daw <matt@shotgunsoftware.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
