Re: Different sort result between PostgreSQL 8.4 and 12.5

On Mon, Jun 27, 2022 at 1:31 PM gzh <gzhcoder(at)126(dot)com> wrote:

> Hi,
>
>
> 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.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>