Sorting Problem

[Tim Edwards]

Currently I'm running PostgreSQL 7.2.3 and having a problem sorting.

I've got two colums of data, one Int4 one Varchar.

When I sort ASC on the varchar I get some strange results. Here a section of
data cut after running a sort. It starts with RM- then does RMT- Then goes
back for more RM-. I've checked for spaces or tabs or anything else odd and
cant find anything that would effect the sort.

63 RM-791
38 RM-AV2100
9 RM-PP401
10 RM-PP402
8 RM-PP404
3 RMT-D10
4 RMT-D108A
5 RMT-D109A
6 RMT-D116A
34 RMT-V402
40 RM-V10
41 RM-V11
45 RM-V12

Here's the sort string I'm using:

SELECT "remote_id", "remote_model" FROM "remote" ORDER BY "remote_model"
ASC;

Any idea or suggestions why it would do this ?

__________________________________________________ _______________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

"Tim Edwards" <mo*****@hotmail.com> writes:

When I sort ASC on the varchar I get some strange results. Here a section of
data cut after running a sort. It starts with RM- then does RMT- Then goes
back for more RM-.

Sounds like you're in en_US locale, or at least something other than C
locale.

Unfortunately this can only be fixed by re-initdb'ing :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Kathy zhu]

Do you mean that soring doesn't work for en_US locale ???
And, does encoding affect sorting at all ??

thanks,
kathy

Tom Lane wrote:

"Tim Edwards" <mo*****@hotmail.com> writes:

When I sort ASC on the varchar I get some strange results. Here a section of
data cut after running a sort. It starts with RM- then does RMT- Then goes
back for more RM-.

Sounds like you're in en_US locale, or at least something other than C
locale.

Unfortunately this can only be fixed by re-initdb'ing :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

--

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Dennis Gearon]

To help my understanding of this type of thing, when he reinits the database, can he get the PG backend to be running with a different LOCALE than the machine's?

Tom Lane wrote:

"Tim Edwards" <mo*****@hotmail.com> writes:

When I sort ASC on the varchar I get some strange results. Here a section of
data cut after running a sort. It starts with RM- then does RMT- Then goes
back for more RM-.

Sounds like you're in en_US locale, or at least something other than C
locale.

Unfortunately this can only be fixed by re-initdb'ing :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Tom Lane]

Dennis Gearon <ge*****@cvc.net> writes:

To help my understanding of this type of thing, when he reinits the database, can he get the PG backend to be running with a different LOCALE than the machine's?

Yeah, he needs to set LANG and/or LC_ALL to be 'C' instead of whatever
it's defaulting to on his machine. Then run initdb with that
environment, and he's set.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Kathy zhu <Ka*******@Sun.COM> writes:

Do you mean that soring doesn't work for en_US locale ???

Oh it works all right, it just doesn't agree with Tim's idea of what
sorted order is ;-)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Kathy zhu]

This brings up another question:

Say initdb with en_US locale, and we have localized strings for different
languages store in the db.

If we have a client in Germany, and want to see the text sorted in german. I
mean that we want do db soring for german strings and display the result in the
browser. How are we going to handle that ??

thanks,
kathy


Dennis Gearon wrote:

Danke, Spacibo, gracias, thanks.

Tom Lane wrote:

Dennis Gearon <ge*****@cvc.net> writes:

You mean in his own local environment? So all his programs, console
operations, etc, will have the new encoding? Or 'LANG/LC_ALL' for
Posgres specifically?

I mean he needs to run initdb with C as the selected locale. It has
nothing to do with what environment his other programs run in.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

--

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org