By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,751 Members | 1,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,751 IT Pros & Developers. It's quick & easy.

Sorting Problem

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
6 Replies

P: n/a
"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)

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

P: n/a
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)

Nov 11 '05 #4

P: n/a
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

Nov 11 '05 #5

P: n/a
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

Nov 11 '05 #6

P: n/a
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

Nov 11 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.