Sorting Problem

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Tim Edwards
    #1

    Sorting Problem

    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_mod el" FROM "remote" ORDER BY "remote_mod el"
    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

    Tags: None
    • Tom Lane
      #2
      Re: Sorting Problem

      "Tim Edwards" <mor4321@hotmai l.com> writes:[color=blue]
      > 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-.[/color]

      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 YourEmailAddres sHere" to majordomo@postg resql.org)

        Comment

        • Kathy zhu
          #3
          Re: Sorting Problem

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

          thanks,
          kathy

          Tom Lane wrote:[color=blue]
          > "Tim Edwards" <mor4321@hotmai l.com> writes:
          >[color=green]
          >>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-.[/color]
          >
          >
          > 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 YourEmailAddres sHere" to majordomo@postg resql.org)[/color]


          --



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

            Comment

            • Dennis Gearon
              #4
              Re: Sorting Problem

              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:
              [color=blue]
              > "Tim Edwards" <mor4321@hotmai l.com> writes:
              >[color=green]
              >>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-.[/color]
              >
              >
              > 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 YourEmailAddres sHere" to majordomo@postg resql.org)
              >[/color]


              ---------------------------(end of broadcast)---------------------------
              TIP 2: you can get off all lists at once with the unregister command
              (send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

                Comment

                • Tom Lane
                  #5
                  Re: Sorting Problem

                  Dennis Gearon <[email protected] t> writes:[color=blue]
                  > 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?[/color]

                  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?

                  PostgreSQL: Not Found
                  http://www.postgresql.org/docs/faqs/FAQ.html


                    Comment

                    • Tom Lane
                      #6
                      Re: Sorting Problem

                      Kathy zhu <Kathy.zhu@Sun. COM> writes:[color=blue]
                      > Do you mean that soring doesn't work for en_US locale ???[/color]

                      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?

                      PostgreSQL: Not Found
                      http://www.postgresql.org/docs/faqs/FAQ.html


                        Comment

                        • Kathy zhu
                          #7
                          Re: Sorting Problem

                          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:[color=blue]
                          > Danke, Spacibo, gracias, thanks.
                          >
                          > Tom Lane wrote:
                          >[color=green]
                          >> Dennis Gearon <[email protected] t> writes:
                          >>[color=darkred]
                          >>> 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?[/color]
                          >>
                          >>
                          >>
                          >> 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
                          >>[/color]
                          >
                          >
                          > ---------------------------(end of broadcast)---------------------------
                          > TIP 7: don't forget to increase your free space map settings[/color]


                          --



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

                          PostgreSQL: PostgreSQL Mailing List Archives
                          http://archives.postgresql.org


                            Comment

                            Previous template Next
                            Working...