On Thu, Sep 16, 2010 at 5:46 AM, Peter Eisentraut <[email protected]> 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 INCLUDING COLLATE 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 C
ja | text | collate ja_JP.utf8
postgres=# SELECT name, setting FROM pg_settings WHERE name IN
('lc_ctype', 'lc_collate');
name | setting
------------+---------
lc_collate | C
lc_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 <= HERE
ja | 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
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
