Re: How to pass around collation information

On Wed, Jun 2, 2010 at 3:46 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On fre, 2010-05-28 at 20:59 +0300, Peter Eisentraut wrote:
>> The feature I'm thinking of is what
>> people might call "per-column locale", and the SQL standard defines
>> that.  It would look like this:
>>
>> CREATE TABLE test (
>>     a varchar COLLATE de,
>>     b varchar COLLATE fr
>> );
>>
>> SELECT * FROM test WHERE a > 'baz' ORDER BY b;
>
> Perhaps it's also worth pointing out there could be use cases other than
> supporting multiple natural languages.  For example, it is frequently
> requested to be able to sort in ways that doesn't ignore special
> characters, binary sort, or perhaps special file name sort that treats
> '/' special in some way.  So it could be quite useful to be able to say
>
> CREATE TABLE something (
>    description text COLLATE en,
>    code char(6) COLLATE binary,
>    file text COLLATE filename_sort
> );
>
> or even something like
>
> CREATE DOMAIN filename AS text COLLATE filename_sort;
>
> These are examples where having the collation attached to the column
> would appear to make more sense then having it attached only to
> operations.

But in the end the only purpose of setting it on a column is to set
which one will be used for operations on that column. And the user
might still override it for a particular query.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company