Re: ORDER BY problem

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