|
Hi, I have a PostgreSQL 8.0.3 running on an older debian server
and have some problems with unicode databases and character conversions. First up, some backgrund info about my server and
installation: test=# \set VERSION = 'PostgreSQL 8.0.3 on
i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13)' AUTOCOMMIT = 'on' VERBOSITY = 'default' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = '>> ' HISTSIZE = '500' LASTOID = '0' DBNAME = 'test' USER = 'postgres' HOST = '/var/run/postgresql' PORT = '5432' ENCODING = 'UNICODE' test=# \! uname -a Linux xx 2.4.27-2-686-smp #1 SMP XX
i686 GNU/Linux test=# \! locale LANG=POSIX LC_CTYPE="POSIX" LC_NUMERIC="POSIX" LC_TIME="POSIX" LC_COLLATE="POSIX" LC_MONETARY="POSIX" LC_MESSAGES="POSIX" LC_PAPER="POSIX" LC_NAME="POSIX" LC_ADDRESS="POSIX" LC_TELEPHONE="POSIX" LC_MEASUREMENT="POSIX" LC_IDENTIFICATION="POSIX" LC_ALL= My problem is, that the lowercase versions of non-ascii
characters are broken. Specifically I found, that when lower() is invoked on a text
with non-ascii characters, the operating system's locale is used for converting
each octet in the string to lowercase in stead of using the locale of the
database to convert each character in the string to lowercase. This caused the
danish lower case o with slash "ø", which in unicode is represented
as the latin1-readable octets "ø", to be converted to the
latin1-readable octets "ã¸", which then in turn was (tried) to be
interpreted as a unicode character - but the octects "ã¸" does not
represent a unicode character in utf8. The lower case version of "ø"
is of course just itself. To get around this problem, I had to create a function
ulower: create or replace function
ulower(text) returns text as 'begin return convert(lower(convert($1,''utf8'',''latin1'')),''latin1'',''utf8''); end;' language plpgsql immutable; Not a very nice solution and it of course only works for
latin1-compatible utf8-encoded strings. First up, I would like to avoid this whole issue. How could
this be circumvented, any settings I can flick around? Then I tried to apply this immutable function as a
functional index on a varchar: test# create index mytable_mycolumn_lower_idx
on mytable(ulower(mycolumn)); ERROR: could not convert UTF-8
character 0x00e2 to ISO8859-1 I had a lot of data in the table before creating this index,
and apparently one of the rows contained unicode character U+00E2 - which is
latin small letter a with circumflex = â. This is a perfectly legal
latin1-character (as any Unicode character below U+0100 is). My second question is then, why did it fail to convert this
character to latin1? Thanks in advance for any help Regards, Morten Barklund Head of Development TBWA\ |
