I have had a Perl Website working for 7 years and have had no problems
until a few weeks ago I replaced my database server with a newer one.
Database server (old): PostgreSQL 8.4 32bit
Database server (new): PostgreSQL 12.5 64bit
I run following sql in PostgreSQL 8.4 and PostgreSQL 12.5, it returns different sort results.
--PostgreSQL 8.4
---------------
pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;
ascid
-----------
"! ascid"
"001"
(2 rows)
--PostgreSQL 12.5
---------------
pg_db=# select ascid from test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;
ascid
-----------
"001"
"! ascid"
(2 rows)
What is the reason for this and is there any easy way to maintain compatibility?
Are these two really running on the same operating system?
This looks a lot like the locale changes included in newer versions of glibc, and is in that case dependent on an upgrade of the operating system, not an upgrade of PostgreSQL. See https://wiki.postgresql.org/wiki/Locale_data_changes for details.