Skip site navigation (1) Skip section navigation (2)

ORDER BY collation order

From: Joe <dev(at)freedomcircle(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: ORDER BY collation order
Date: 2008-09-19 00:48:58
Message-ID: 48D2F6FA.7050808@freedomcircle.net (view raw or flat)
Thread:
Lists: pgsql-sql
Hi,

I just found that two identical queries on two PG 8.2.7 databases with 
the same data and same encoding, one running on Debian and the other on 
FreeBSD, returned rows in a different order, even though both queries 
had an ORDER BY clause.  Essentially, on FreeBSD a varchar starting with 
a double-quote character came first, ahead of a string starting with a 
number and a string starting with 'A', whereas on Debian the 
double-quote came last.

Some research led to the following paragraph in the documentation:

Character-string data is sorted according to the locale-specific 
collation order that was established when the database cluster was 
initialized.

I guess that means the encoding of the respective template0 database is 
what determines the sort order (UTF8 on FreeBSD and LATIN1 on Debian), 
right?  Unfortunately, I'm unable to change the FreeBSD cluster since 
it's shared with others at our hosting provider.  Is there some way to 
override the cluster setting, or plans to allow for database-specific 
collation orders?

Joe

Responses

pgsql-sql by date

Next:From: Scott MarloweDate: 2008-09-19 02:26:32
Subject: Re: ORDER BY collation order
Previous:From: Scott MarloweDate: 2008-09-18 19:47:52
Subject: Re: Doubts about FK

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group