[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re:Re: Different sort result between PostgreSQL 8.4 and 12.5
From: gzh <gzhcoder () 126 ! com>
Date: 2022-06-28 3:58:49
Message-ID: 5631d95e.2ff3.181a8775ec6.Coremail.gzhcoder () 126 ! com
[Download RAW message or body]
[Attachment #2 (text/plain)]
Dear Magnus£º
The information you sent through to me was perfect.
After I checked the operating system, I found that they are really different.
--PostgreSQL 8.4
LANG=ja_JP.UTF-8
--PostgreSQL 12.5
LANG=en_US.UTF-8
After I added the following syntax after the "order by ascid" in PostgreSQL 12.5 \
database, I got the same result as PostgreSQL 8.4
COLLATE "ja-JP-x-icu"
Thank you for being so helpful.
2022-06-27 19:33:01£¬"Magnus Hagander" <magnus@hagander.net> £º
On Mon, Jun 27, 2022 at 1:31 PM gzh <gzhcoder@126.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/
Work: https://www.redpill-linpro.com/
[Attachment #3 (text/html)]
<div style="line-height:1.7;color:#000000;font-size:14px;font-family:Arial"><p \
style="margin: 0;"><br></p><p style="margin: 0;">Dear Magnus£º</p><div style="margin: \
0;"><div style="margin: 0;"><br></div><div style="margin: 0;">The information you \
sent through to me was perfect. </div><div style="margin: 0;">After I checked \
the operating system, I found that they are really different.</div><div \
style="margin: 0;"><br></div><div style="margin: 0;">--PostgreSQL 8.4</div><div \
style="margin: 0;">LANG=ja_JP.UTF-8</div><div style="margin: 0;"><br></div><div \
style="margin: 0;">--PostgreSQL 12.5</div><div style="margin: \
0;">LANG=en_US.UTF-8</div><div style="margin: 0;"><br></div><div style="margin: \
0;">After I added the following syntax after the "order by ascid" in PostgreSQL 12.5 \
database, I got the same result as PostgreSQL 8.4</div><div style="margin: \
0;"><br></div><div style="margin: 0;">COLLATE "ja-JP-x-icu"</div><div style="margin: \
0;"><br></div><div style="margin: 0;">Thank you for being so helpful.</div></div><p \
style="margin: 0;"><br></p><p style="margin: 0;"><br></p><p> 2022-06-27 \
19:33:01£¬"Magnus Hagander" <magnus@hagander.net> £º</p><blockquote \
id="isReplyContent" style="PADDING-LEFT: 1ex; MARGIN: 0px 0px 0px 0.8ex; BORDER-LEFT: \
#ccc 1px solid"><div dir="ltr"><div dir="ltr"><br></div><br><div \
class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Jun 27, 2022 at 1:31 PM \
gzh <<a href="mailto:gzhcoder@126.com">gzhcoder@126.com</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div \
style="line-height:1.7;color:rgb(0,0,0);font-size:14px;font-family:Arial"><p \
style="margin:0px">Hi, </p><p style="margin:0px"><br></p><p style="margin:0px">I \
have had a Perl Website working for 7 years and have had no problems</p><div \
style="margin:0px">until a few weeks ago I replaced my database server with a newer \
one.</div><p style="margin:0px"><br></p><p style="margin:0px">Database server (old): \
PostgreSQL 8.4 32bit</p><p style="margin:0px">Database server (new): PostgreSQL 12.5 \
64bit</p><p style="margin:0px"><br></p><p style="margin:0px">I run following sql in \
PostgreSQL 8.4 and PostgreSQL 12.5, it returns different sort results.</p><p \
style="margin:0px"><br></p><p style="margin:0px">--PostgreSQL 8.4</p><p \
style="margin:0px">---------------</p><p style="margin:0px">pg_db=# select ascid from \
test_order where oo_m.ascid in ('"! ascid"','"001"') order by ascid;</p><p \
style="margin:0px"> ascid</p><p style="margin:0px">-----------</p><p \
style="margin:0px"> "! ascid"</p><p style="margin:0px"> "001"</p><p \
style="margin:0px">(2 rows)</p><p style="margin:0px"><br></p><p \
style="margin:0px">--PostgreSQL 12.5</p><p style="margin:0px">---------------</p><p \
style="margin:0px">pg_db=# select ascid from test_order where oo_m.ascid in ('"! \
ascid"','"001"') order by ascid;</p><p style="margin:0px"> ascid</p><p \
style="margin:0px">-----------</p><p style="margin:0px"> "001"</p><p \
style="margin:0px"> "! ascid"</p><p style="margin:0px">(2 rows)</p><p \
style="margin:0px"><br></p><p style="margin:0px">What is the reason for this and is \
there any easy way to maintain compatibility?</p><div \
style="margin:0px"><br></div></div></blockquote><div><br></div><div>Are these two \
really running on the same operating system?</div><div><br></div><div>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 <a href="https://wiki.postgresql.org/wiki/Locale_data_changes">https://wiki.postgresql.org/wiki/Locale_data_changes</a> \
for details.</div><div> </div></div>-- <br><div dir="ltr" \
class="gmail_signature"><div dir="ltr"><div> Magnus Hagander<br> Me: <a \
href="http://www.hagander.net/" \
target="_blank">https://www.hagander.net/</a><br> Work: <a \
href="http://www.redpill-linpro.com/" \
target="_blank">https://www.redpill-linpro.com/</a></div></div></div></div> \
</blockquote></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic