problem with dots in order by

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

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.

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: