Thread: problem with dots in order by

problem with dots in order by

From

Ruben Oliveira

Date:

15 February 2005, 16:05:18

Hello everybody !

I have this order by :
select lalala from tablex order by field_y;
where field_y is a text column.

that returns something like :
2.1004.11
21.00.461
2.1006.21

in  PostgreSQL 7.3.2 in Linux Mandrake 9.1

but in PostgreSQL 8.0.0 in Windows XP it works as I expected ...

2.1004.11
2.1006.21
21.00.461

the two config share the same Encoding when the database was created and
the client enconding is the same in the two cases :

H:\Documents and Settings\User>psql -l -U postgres
        List of databases
   Name    |  Owner   | Encoding
-----------+----------+-----------
 bd2005    | postgres | SQL_ASCII


Can anybody tell me if there is something I can do in the Linux config
to have the same order by result ??
Using XP is only for tests... I love my Mandrake server :P

Re: problem with dots in order by

From

Tom Lane

Date:

15 February 2005, 16:34:28

Ruben Oliveira <ruben_dig@netcabo.pt> writes:
> Can anybody tell me if there is something I can do in the Linux config
> to have the same order by result ??

Sort order is determined by locale, not encoding.  You did the Linux
initdb in the wrong locale (possibly something like en_US where you'd
rather have had C locale).

            regards, tom lane

Re: problem with dots in order by

From

Stephan Szabo

Date:

15 February 2005, 16:38:40

On Tue, 15 Feb 2005, Ruben Oliveira wrote:

> Hello everybody !
>
> I have this order by :
> select lalala from tablex order by field_y;
> where field_y is a text column.
>
> that returns something like :
> 2.1004.11
> 21.00.461
> 2.1006.21
>
> in  PostgreSQL 7.3.2 in Linux Mandrake 9.1
>
> but in PostgreSQL 8.0.0 in Windows XP it works as I expected ...
>
> 2.1004.11
> 2.1006.21
> 21.00.461
>
> the two config share the same Encoding when the database was created and
> the client enconding is the same in the two cases :

It's not encoding that's important.  It's locale (LC_COLLATE).
The linux box is probably running the server with en_US which sorts like
you've shown the above (ignoring most/all symbols and spaces in first pass
sorting).

You could re-initdb in "C" locale to change the sort ordering.

Re: problem with dots in order by

From

Ruben Oliveira

Date:

15 February 2005, 17:01:25

Thanks for the quick reply :)

I missed the localization part of the manual , shame on me !!
http://www.postgresql.org/docs/current/static/charset.html

LC_COLLATE String sort order

I did:
 set|grep LC
and because I am portuguese it returns pt_PT

just a quick question if you please :
is there any implication in changing Postgresql to the C locale ...
other than
the order by and to_char handling ?

I'm running Postgresql as an ERP Database  done by me and any mistakes I
do:  I'm dead meat :P

If you have experience with this I welcome any reply ...
Other than that I will try in some other machine the
 initdb --locale=C
just to be safe ...

Thanks again ,
Ruben Oliveira
-

Tom Lane wrote:

>Ruben Oliveira <ruben_dig@netcabo.pt> writes:
>
>
>>Can anybody tell me if there is something I can do in the Linux config
>>to have the same order by result ??
>>
>>
>
>Sort order is determined by locale, not encoding.  You did the Linux
>initdb in the wrong locale (possibly something like en_US where you'd
>rather have had C locale).
>
>            regards, tom lane
>
>
>