Order By and Comparisson

howardnews@selestial.com wrote:

just as I thought I had postgres mastered :) the ordering of strings is
causing me some confusion.

Can someone explain how the database orders strings in the ORDER BY command.

My example:

My database is encoding is UTF-8, and default language is english,

If I have a text column in a table with the following rows:

'a'
'A'
'~'

Then in UTF-8, I would expect the order to give me

'A'
'a'
'~'

But instead I get:

'~'
'a'
'A'

Is there anywhere in the documentation I can get a more detailed
explanation of this?

http://www.postgresql.org/docs/current/static/collation.html#AEN33298

The ordering depends on the collation.
Which collations are available and how they order depends on your operating system.

What is your OS and what do you get for
SHOW lc_collate;

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 07/04/2014 11:58, Albe Laurenz wrote:

http://www.postgresql.org/docs/current/static/collation.html#AEN33298
The ordering depends on the collation. Which collations are available
and how they order depends on your operating system. What is your OS
and what do you get for SHOW lc_collate; Yours, Laurenz Albe

Hi Laurenz,

Thanks for the reply, I get the following from show lc_collate:

en_GB.UTF-8

Howard.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

howardnews@selestial.com wrote:

http://www.postgresql.org/docs/current/static/collation.html#AEN33298
The ordering depends on the collation. Which collations are available
and how they order depends on your operating system. What is your OS
and what do you get for SHOW lc_collate; Yours, Laurenz Albe

Thanks for the reply, I get the following from show lc_collate:

en_GB.UTF-8

Then that's how that collation sorts.

You should get the same results with the UNIX command 'sort'.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 07 Apr 2014 11:04:23 +0100
"howardnews@selestial.com" <howardnews@selestial.com> wrote:

Hi,

just as I thought I had postgres mastered :) the ordering of strings is
causing me some confusion.

Can someone explain how the database orders strings in the ORDER BY command.

My example:

My database is encoding is UTF-8, and default language is english,

If I have a text column in a table with the following rows:

'a'
'A'
'~'

Then in UTF-8, I would expect the order to give me

'A'
'a'
'~'

But instead I get:

'~'
'a'
'A'

Is there anywhere in the documentation I can get a more detailed
explanation of this?

Expect for the unexpected. SQL alphabetical sorting can get pretty complicated,
as stated in this note from Oracle 10g Release 2 docs[1]docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm:

"In the ASCII standard, all uppercase letters appear before any lowercase letters.
In the EBCDIC standard, the opposite is true: all lowercase letters appear before
any uppercase letters".

[1]: docs.oracle.com/cd/B19306_01/server.102/b14225/ch5lingsort.htm

--
Alberto Cabello Sánchez
<alberto@unex.es>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general