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