Re: Per-column collation, work in progress

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