From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Joe <dev(at)freedomcircle(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ORDER BY collation order |
Date: | 2008-09-19 02:26:32 |
Message-ID: | dcc563d10809181926y5d1df6eud0d9a19dbb80ec59@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Thu, Sep 18, 2008 at 6:48 PM, Joe <dev(at)freedomcircle(dot)net> wrote:
> 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?
no, not encoding, locale, such as en_US or C determine sort order.
You can use varchar_pattern_ops and ~*~ operator.
Search for those in the docs.
From | Date | Subject | |
---|---|---|---|
Next Message | Joe | 2008-09-19 04:35:22 | Re: ORDER BY collation order |
Previous Message | Joe | 2008-09-19 00:48:58 | ORDER BY collation order |