On Thu, Sep 18, 2008 at 6:48 PM, Joe <[EMAIL PROTECTED]> 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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
