Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEG44-0005yv-9h for pgsql-sql@arkaria.postgresql.org; Sat, 07 Jan 2023 20:45:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pEG43-0006nX-4J for pgsql-sql@arkaria.postgresql.org; Sat, 07 Jan 2023 20:45:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEG42-0006n7-Rp for pgsql-sql@lists.postgresql.org; Sat, 07 Jan 2023 20:45:14 +0000 Received: from mout.perfora.net ([74.208.4.196]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pEG40-00006a-4r for pgsql-sql@lists.postgresql.org; Sat, 07 Jan 2023 20:45:13 +0000 Received: from [10.221.133.171] ([142.105.135.230]) by mrelay.perfora.net (mreueus004 [74.208.5.2]) with ESMTPSA (Nemesis) id 1MUGJb-1pNIbU0cJn-00RGqD for ; Sat, 07 Jan 2023 21:45:11 +0100 Message-ID: <1b6f965c-c97b-1b3f-e955-14402544979c@sopchak.me> Date: Sat, 7 Jan 2023 15:45:10 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.6.0 Subject: Re: Order By weirdness? Content-Language: en-US To: pgsql-sql@lists.postgresql.org References: <25e94e6e-5b18-9d45-0b7b-c23bd4c9a17b@sopchak.me> From: Carl Sopchak In-Reply-To: Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit X-Provags-ID: V03:K1:XNjX7iruIGfhp420R6DMtl0JbhKLl5gN34UmiU73ZvlgiSEclgm VYYUrhAPIB4FTQztIjrPLvMI0FJBLr4ZD2CRfycgcYG8+cMf0PXlNeFPB0MKqrPqb44lgT9 Cl2Klpf7Vq4gZIhIyXhuzxBgMGN8HRS6/7QajIOaMCzVKm66cK+HlltKIerL9IP0PTxBjkb w5GULAymP/xvAPjlKmmaQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:L+fZwLBh39U=;a+/pwjQWdAIAease+qFixTgPSIw uG4ZwAiqkiN0mdxRO5NWmne0orOiAOmfmD1V1tYzkiNT1OqdStJ2e4KqrIj3ViZlBmjN0AC49 E1WuN94lgTYb3zOFgCTuYY0mZqED1h6CTbtEV1Cvq0d95RsNThjszmOFbHl4HejCVcmkodDUE Vs/HuOoTIq30JDUNj/hJYovqGs4jlBQWrb7mIjupkKgeM8pBe+JwQ5A1TVGppGLdALTsM4tSu rgPQ19z9lz0yLlXoMPzi9XCHeMMMx0IJ5Q712ad0t8GaBJbExO1Hv2hrzJCS6njanJLCj+tEO hTbefekuURvktJJqImQbL/ziCIVTrioO8HjjO34wtfDN0z7kc3uAXRztVILVT2ZSAe86BMmks YC7DWOfkfEhySC17a4tyHF/kKqbVq936oJJNc5H32oBUBjr4sxVl+b+jfI3WRfeKTdto7iPx9 QzvZvglMvYp5zO66taLkRbDk3auFOB6TLhqreFpyjjthvzncBTCHx/x5iEfRGcp2rglavL420 bwraN18AgEQig98Rpuf/Sn7aw8vZXDT7HAEh02nnMEQ/pynh4WtadiZoNAuEpLvahAK5cZJnh EczZVZRbrWvS7oDPwkzAedso6RCpta6K8VFBIA/T3NWb35mteVbdF0fHwsIz8wdQMnnY5MuCW I3LvzH3snTtNsusHxwy5wnV3rIDILt68fiCBdvnX9g== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk

Thanks, that did the trick. The surprising thing in the link is "Most locales would ignore the leading # for sorting. " I guess I've been around too long and hadn't noticed. :-)

On 1/7/23 15:38, Erik Brandsberg wrote:

This will relate to collation order, which is something that you can specify. Please see: https://stackoverflow.com/questions/17225652/how-can-i-sort-the-postgres-column-with-certain-special-characters

On Sat, Jan 7, 2023 at 3:35 PM Carl Sopchak <carl@sopchak.me> wrote:

I'm seeing something (very) unexpected with ORDER BY. If I run this query:

select txt
from ( values('x12345'), ('xz1234'), ('x23456'), ('xz2345'), ('x34567'), ('xz3456') ) a(txt)
order by txt;

I get expected results with x<#> being sorted before xz. However, if I replace the z's with ~, giving

select txt
from ( values('x12345'), ('x~1234'), ('x23456'), ('x~2345'), ('x34567'), ('x~3456') ) a(txt)
order by txt;

I get this???

txt
--------
x~1234
x12345
x~2345
x23456
x~3456
x34567

Which appears to mean that ~ is treated differently than z (basically ~ is ignored). Same if I use other special characters, such as @.

Up until stumbling into this, I have never seen such behavior from a database. (Windows OS, yes, but I won't go there...) Character-based text always sorted in an alphabetic order (which puts special characters in different places in the ordering depending on encoding, but it's consistent).

Two questions (which may be the same way of asking the same question):

- How is this correct? I can see where this could be useful in limited scenarios, but IMHO it makes no sense as a default sort order.

- What do I need to do to get a strictly character-based sort in ORDER BY?

I am using postgres version 14.3 on Fedora 37.

Thanks for the help.

Carl