Re: Per-column collation, proof of concept

From: Peter Eisentraut <peter_e(at)gmx(dot)net>
To: Jaime Casanova <jaime(at)2ndquadrant(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Per-column collation, proof of concept
Date: 2010-08-03 16:32:13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On mån, 2010-08-02 at 01:43 -0500, Jaime Casanova wrote:
> nowadays, CREATE DATABASE has a lc_collate clause. is the new collate
> clause similar as the lc_collate?
> i mean, is lc_collate what we will use as a default?

Yes, if you do not specify anything per column, the database default is

How to integrate the per-database or per-cluster configuration with the
new system is something to figure out in the future.

> if yes, then probably we need to use pg_collation there too because
> lc_collate and the new collate clause use different collation names.
> """
> postgres=# create database test with lc_collate 'en_US.UTF-8';
> test=# create table t1 (col1 text collate "en_US.UTF-8");
> ERROR: collation "en_US.UTF-8" does not exist
> test=# create table t1 (col1 text collate "en_US.utf8");
> """

This is something that libc does for you. The locale as listed by
locale -a is called "en_US.utf8", but apparently libc takes
"en_US.UTF-8" as well.

> also i got errors from regression tests when MULTIBYTE=UTF8
> (attached). it seems i was trying to create locales that weren't
> defined on locales.txt (from were was fed that file?). i added a line
> to that file (for es_EC.utf8) then i create a table with a column
> using that collate and execute "select * from t2 where col1 > 'n'; "
> and i got this error: "ERROR: could not create locale "es_EC.utf8""
> (of course, that last part was me messing the things up, but it show
> we shouldn't be using a file locales.txt, i think)

It might be that you don't have those locales installed in your system.
locales.txt is created by using locale -a. Check what that gives you.

> i can attach a collate to a domain but i can't see where are we
> storing that info (actually it says it's not collatable):

Domain support is not done yet.

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Richard 2010-08-03 16:34:11 Two problems when using Postgresql8.3.7, Please help me!
Previous Message Hitoshi Harada 2010-08-03 16:30:54 Re: Status report on writeable CTEs