Thread: ORDER BY problem

ORDER BY problem

From

Thomas Beutin

Date:

01 March 2004, 14:20:14

Hi,

when i do the following query i get an wrong result order on my postgres
system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)

tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
               name               |              upper
----------------------------------+----------------------------------
 Americabound Tours, Inc.         | AMERICABOUND TOURS, INC.
 American Airlines                | AMERICAN AIRLINES
 American Express Tour Operador   | AMERICAN EXPRESS TOUR OPERADOR
 American Receptive Tours         | AMERICAN RECEPTIVE TOURS
 American Ring Travel, Inc.       | AMERICAN RING TRAVEL, INC.
 American Sightseeing Chicago     | AMERICAN SIGHTSEEING CHICAGO
 AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
 America West Airlines, Inc.      | AMERICA WEST AIRLINES, INC.
(8 rows)

The last line should IMHO be the first; but line 7 is in the correct place
compared to lines 2-6.
Has anybody any hint for me to resolve the problem?

Any help is greatly appreciated.
-tb
--
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

Re: ORDER BY problem

From

Stephan Szabo

Date:

01 March 2004, 14:28:40

On Mon, 1 Mar 2004, Thomas Beutin wrote:

> when i do the following query i get an wrong result order on my postgres
> system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)
>
> tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
>                name               |              upper
> ----------------------------------+----------------------------------
>  Americabound Tours, Inc.         | AMERICABOUND TOURS, INC.
>  American Airlines                | AMERICAN AIRLINES
>  American Express Tour Operador   | AMERICAN EXPRESS TOUR OPERADOR
>  American Receptive Tours         | AMERICAN RECEPTIVE TOURS
>  American Ring Travel, Inc.       | AMERICAN RING TRAVEL, INC.
>  American Sightseeing Chicago     | AMERICAN SIGHTSEEING CHICAGO
>  AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
>  America West Airlines, Inc.      | AMERICA WEST AIRLINES, INC.
> (8 rows)
>
> The last line should IMHO be the first; but line 7 is in the correct place
> compared to lines 2-6.
> Has anybody any hint for me to resolve the problem?

You're probably using a locale such as en_US under which you get an
ordering where spaces nor case are not considered at first IIRC so America
West is America followed by a W, American is America followed by an n.

If you want ordering by the byte values, you'll need to initdb in with the
"C" locale.

Re: ORDER BY problem

From

Thomas Beutin

Date:

01 March 2004, 15:20:21

On Mon, Mar 01, 2004 at 07:28:37AM -0800, Stephan Szabo wrote:
> On Mon, 1 Mar 2004, Thomas Beutin wrote:
>
> > when i do the following query i get an wrong result order on my postgres
> > system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)
> >
> > tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
> >                name               |              upper
> > ----------------------------------+----------------------------------
> >  Americabound Tours, Inc.         | AMERICABOUND TOURS, INC.
> >  American Airlines                | AMERICAN AIRLINES
> >  American Express Tour Operador   | AMERICAN EXPRESS TOUR OPERADOR
> >  American Receptive Tours         | AMERICAN RECEPTIVE TOURS
> >  American Ring Travel, Inc.       | AMERICAN RING TRAVEL, INC.
> >  American Sightseeing Chicago     | AMERICAN SIGHTSEEING CHICAGO
> >  AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
> >  America West Airlines, Inc.      | AMERICA WEST AIRLINES, INC.
> > (8 rows)
> >
> > The last line should IMHO be the first; but line 7 is in the correct place
> > compared to lines 2-6.
> > Has anybody any hint for me to resolve the problem?
>
> You're probably using a locale such as en_US under which you get an
> ordering where spaces nor case are not considered at first IIRC so America
> West is America followed by a W, American is America followed by an n.
>
> If you want ordering by the byte values, you'll need to initdb in with the
> "C" locale.
Yes, You're right, my locale at the initdb time was "de_DE@euro", and that's
what i need for the german unlauts. When i change the locale (dig into
localedef et.al.) is it enough to install the new locale stuff and restart
the postmaster or must i dump and reload the whole database?

Greetings from Berlin,
-tb
--
Thomas Beutin                             tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.

Re: ORDER BY problem

From

Stephan Szabo

Date:

01 March 2004, 16:11:06

On Mon, 1 Mar 2004, Thomas Beutin wrote:

> On Mon, Mar 01, 2004 at 07:28:37AM -0800, Stephan Szabo wrote:
> > On Mon, 1 Mar 2004, Thomas Beutin wrote:
> >
> > > when i do the following query i get an wrong result order on my postgres
> > > system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)
> > >
> > > tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
> > >                name               |              upper
> > > ----------------------------------+----------------------------------
> > >  Americabound Tours, Inc.         | AMERICABOUND TOURS, INC.
> > >  American Airlines                | AMERICAN AIRLINES
> > >  American Express Tour Operador   | AMERICAN EXPRESS TOUR OPERADOR
> > >  American Receptive Tours         | AMERICAN RECEPTIVE TOURS
> > >  American Ring Travel, Inc.       | AMERICAN RING TRAVEL, INC.
> > >  American Sightseeing Chicago     | AMERICAN SIGHTSEEING CHICAGO
> > >  AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
> > >  America West Airlines, Inc.      | AMERICA WEST AIRLINES, INC.
> > > (8 rows)
> > >
> > > The last line should IMHO be the first; but line 7 is in the correct place
> > > compared to lines 2-6.
> > > Has anybody any hint for me to resolve the problem?
> >
> > You're probably using a locale such as en_US under which you get an
> > ordering where spaces nor case are not considered at first IIRC so America
> > West is America followed by a W, American is America followed by an n.
> >
> > If you want ordering by the byte values, you'll need to initdb in with the
> > "C" locale.
> Yes, You're right, my locale at the initdb time was "de_DE@euro", and that's
> what i need for the german unlauts. When i change the locale (dig into
> localedef et.al.) is it enough to install the new locale stuff and restart
> the postmaster or must i dump and reload the whole database?

I think you need to dump any databases in the database cluster, re-initdb
and then reload the databases.  However, I'm not sure how that's going to
interact with special characters (since "C" will sort them in their byte
position and "de_DE" ignores spaces).

Re: ORDER BY problem

From

Bruno Wolff III

Date:

01 March 2004, 18:09:16

On Mon, Mar 01, 2004 at 16:01:35 +0100,
  Thomas Beutin <tyrone@laokoon.IN-Berlin.DE> wrote:
> Hi,
>
> when i do the following query i get an wrong result order on my postgres
> system (PostgreSQL 7.2.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2)
>
> tb_test=# SELECT name, upper(name) FROM tb_wurst ORDER BY upper(name);
>                name               |              upper
> ----------------------------------+----------------------------------
>  Americabound Tours, Inc.         | AMERICABOUND TOURS, INC.
>  American Airlines                | AMERICAN AIRLINES
>  American Express Tour Operador   | AMERICAN EXPRESS TOUR OPERADOR
>  American Receptive Tours         | AMERICAN RECEPTIVE TOURS
>  American Ring Travel, Inc.       | AMERICAN RING TRAVEL, INC.
>  American Sightseeing Chicago     | AMERICAN SIGHTSEEING CHICAGO
>  AMERICANTOURS INTERNATIONAL INC. | AMERICANTOURS INTERNATIONAL INC.
>  America West Airlines, Inc.      | AMERICA WEST AIRLINES, INC.
> (8 rows)
>
> The last line should IMHO be the first; but line 7 is in the correct place
> compared to lines 2-6.
> Has anybody any hint for me to resolve the problem?

This is a locale issue. You probably are using en_US for the cluster
instead of C. In 7.2 you need to do an initdb to change this.

>
> Any help is greatly appreciated.
> -tb
> --
> Thomas Beutin                             tb@laokoon.IN-Berlin.DE
> Beam me up, Scotty. There is no intelligent live down in Redmond.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster