ORDER BY problem

[Thomas Beutin]

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 [e-mail]
Beam me up, Scotty. There is no intelligent live down in Redmond.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Stephan Szabo]

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.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Thomas Beutin]

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 [e-mail]
Beam me up, Scotty. There is no intelligent live down in Redmond.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Stephan Szabo]

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).

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

http://archives.postgresql.org

[Bruno Wolff III]

On Mon, Mar 01, 2004 at 16:01:35 +0100,
Thomas Beutin <ty****@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 [e-mail]
Beam me up, Scotty. There is no intelligent live down in Redmond.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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

http://archives.postgresql.org