On Thu, Sep 16, 2010 at 5:46 AM, Peter Eisentraut <peter_e@gmx.net> wrote:
> Following up on my previous patch [0], here is a fairly complete
> implementation of this feature. The general description and
> implementation outline of the previous message still apply. This patch
> contains documentation and regression tests, which can serve as further
> explanations.
I tested the patch on database with encoding=UTF8 and locale-C.
I have a couple of questions and comments.
* CREATE TABLE (LIKE table_with_collation) doesn't inherit collations. We need to copy collations by default, or add
INCLUDINGCOLLATE option.
* upper() doesn't work if a column has a collation. It still works if a column doesn't have a collation.
postgres=# \d tbl Table "public.tbl"Column | Type | Modifiers
--------+------+--------------------c | text | collate Cja | text | collate ja_JP.utf8
postgres=# SELECT name, setting FROM pg_settings WHERE name IN
('lc_ctype', 'lc_collate'); name | setting
------------+---------lc_collate | Clc_ctype | C
(2 rows)
postgres=# SELECT upper(c) FROM tbl;
ERROR: invalid multibyte character for locale
HINT: The server's LC_CTYPE locale is probably incompatible with the
database encoding.
postgres=# SELECT upper(ja) FROM tbl;
ERROR: invalid multibyte character for locale
HINT: The server's LC_CTYPE locale is probably incompatible with the
database encoding
* Comparison of strings with different collations is forbidden, but assignment is allowed, right?
postgres=# SELECT * FROM tbl WHERE c = ja;
ERROR: collation mismatch between implicit collations "C" and "ja_JP.utf8"
LINE 1: SELECT * FROM tbl WHERE c = ja; ^
HINT: You can override the collation by applying the COLLATE clause
to one or both expressions.
postgres=# INSERT INTO tbl(c, ja) SELECT ja, c FROM tbl;
INSERT 0 6
* psql \d needs a separator between collate and not null modifiers.
postgres=# ALTER TABLE tbl ALTER COLUMN c SET NOT NULL;
ALTER TABLE
postgres=# \d tbl Table "public.tbl"Column | Type | Modifiers
--------+------+--------------------c | text | collate Cnot null <= HEREja | text | collate ja_JP.utf8
> the feature overall only works on Linux/glibc.
We could support it also on MSVC.
http://msdn.microsoft.com/en-us/library/a7cwbx4t(v=VS.90).aspx -- _strcoll_l
http://msdn.microsoft.com/en-us/library/45119yx3(v=VS.90).aspx -- _towupper_l
--
Itagaki Takahiro