Postgres Pro
Downloads
Home   >   mailing lists

Thread: Order By and Comparisson

Order By and Comparisson

From
"howardnews@selestial.com"
Date:
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?

Thanks.

Re: Order By and Comparisson

From
Albe Laurenz
Date:
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

Re: Order By and Comparisson

From
"howardnews@selestial.com"
Date:
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.

Re: Order By and Comparisson

From
Albe Laurenz
Date:
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

Re: Order By and Comparisson

From
Alberto Cabello Sánchez
Date:
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]:

"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>