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?