public inbox for [email protected]
help / color / mirror / Atom feedFrom: Erik Brandsberg <[email protected]>
To: Carl Sopchak <[email protected]>
Cc: [email protected]
Subject: Re: Order By weirdness?
Date: Sat, 7 Jan 2023 15:38:16 -0500
Message-ID: <CAFcck8GQXkUQGsTgtdD65_+9VMPkx8iW8t+_3Oe_=J3oyWf7QA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
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...
On Sat, Jan 7, 2023 at 3:35 PM Carl Sopchak <[email protected]> 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
>
>
>
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Order By weirdness?
In-Reply-To: <CAFcck8GQXkUQGsTgtdD65_+9VMPkx8iW8t+_3Oe_=J3oyWf7QA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox