Re: Per-column collation

On mån, 2010-11-15 at 23:13 +0100, Pavel Stehule wrote:
> a) default encoding for collate isn't same as default encoding of database
>
> it's minimally not friendly - mostly used encoding is UTF8, but in
> most cases users should to write locale.utf8.

I don't understand what you are trying to say. Please provide more
detail.

> b) there is bug - default collate (database collate is ignored)
>
>
> postgres=# show lc_collate;
> lc_collate
> ────────────
> cs_CZ.UTF8
> (1 row)
>
> Time: 0.518 ms
> postgres=# select * from jmena order by v;
> v
> ───────────
> Chromečka
> Crha
> Drobný
> Čečetka
> (4 rows)
>
> postgres=# select * from jmena order by v collate "cs_CZ.utf8";
> v
> ───────────
> Crha
> Čečetka
> Drobný
> Chromečka
> (4 rows)
>
> both result should be same.

I tried to reproduce this here but got the expected results. Could you
try to isolate a complete test script?

> isn't there problem in case sensitive collate name? When I use a
> lc_collate value, I got a error message
>
> postgres=# select * from jmena order by v collate "cs_CZ.UTF8";
> ERROR: collation "cs_CZ.UTF8" for current database encoding "UTF8"
> does not exist
> LINE 1: select * from jmena order by v collate "cs_CZ.UTF8";
>
> problem is when table is created without explicit collate.

Well, I agree it's not totally nice, but we have to do something, and I
think it's logical to use the locale names as collation names by
default, and collation names are SQL identifiers. Do you have any ideas
for improving this?