Re: ORDER BY collation order

On Thu, Sep 18, 2008 at 6:48 PM, Joe <dev@freedomcircle.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.