Re: Sensitive sort problem?

Hi to everyone,

I don't know exactly if i'm sending my problem in the right place, hope you can help me.

I use postgresql db. 7.3.2.

Today I noticed that my database does not sort "case sensitive" anymore. If I have a query like:
Select name from names order by name -- the names are not resulted as they should. 'ax' is after 'Aa' and so one.

I don't know since when is this problem I noticed it just now. I use Suse 8.2 and yesterday evening I made a live update. Could this be the problem? I restored the system and ... the problem remains.

I also have another linux system, there the db works fine.

Does anyone has any ideea???? Are there any settings for something like this?

Thnkx.
Andy.

PostgreSQL 7.3.x and anything below has never had case insensitive sorting - period.
I can't speak for the new 7.4 version?

If you want to add a case insensitive data type try this:
http://gborg.postgresql.org/project/citext/projdisplay.php

Regards
Donald Fraser.
----- Original Message -----
From: Andrei Bintintan
To: pgsql-admin(at)postgresql(dot)org
Sent: Tuesday, November 25, 2003 3:04 PM
Subject: [ADMIN] Sensitive sort problem?

Hi to everyone,

I don't know exactly if i'm sending my problem in the right place, hope you can help me.

I use postgresql db. 7.3.2.

Today I noticed that my database does not sort "case sensitive" anymore. If I have a query like:
Select name from names order by name -- the names are not resulted as they should. 'ax' is after 'Aa' and so one.

I don't know since when is this problem I noticed it just now. I use Suse 8.2 and yesterday evening I made a live update. Could this be the problem? I restored the system and ... the problem remains.

I also have another linux system, there the db works fine.

Does anyone has any ideea???? Are there any settings for something like this?

Thnkx.
Andy.

"Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> PostgreSQL 7.3.x and anything below has never had case insensitive sorting =
> - period.

This is incorrect. In point of fact it will depend on the locale the
database is running under. I think the OP's problem is he re-initdb'd
without noting what locale setting was active.

regards, tom lane

I give an example:

These are the datas from the table names:

id n1 n2
1 AaAa AaAa
2 X X
3 A A
4 a a
5 ab ab
6 _Y _Y
8 ..a ..a
9 .x .x
7 ...a ...a

If I run: select * from names order by n1: the result is:

id n1 n2
4 a a
8 ..a ..a
7 ...a ...a
3 A A
1 AaAa AaAa
5 ab ab
9 .x .x
2 X X
6 _Y _Y

Which I don't think that is right. I don't know what happened. But I find that the ASCII char order is not respected here.

I have another machine on which the result is okay....I don't have a clue.

Andy.

----- Original Message -----
From: Donald Fraser
To: [ADMIN]
Sent: Tuesday, November 25, 2003 6:21 PM
Subject: Re: [ADMIN] Sensitive sort problem?

PostgreSQL 7.3.x and anything below has never had case insensitive sorting - period.
I can't speak for the new 7.4 version?

If you want to add a case insensitive data type try this:
http://gborg.postgresql.org/project/citext/projdisplay.php


Regards
Donald Fraser.
----- Original Message -----
From: Andrei Bintintan
To: pgsql-admin(at)postgresql(dot)org
Sent: Tuesday, November 25, 2003 3:04 PM
Subject: [ADMIN] Sensitive sort problem?

Hi to everyone,

I don't know exactly if i'm sending my problem in the right place, hope you can help me.

I use postgresql db. 7.3.2.

Today I noticed that my database does not sort "case sensitive" anymore. If I have a query like:
Select name from names order by name -- the names are not resulted as they should. 'ax' is after 'Aa' and so one.

I don't know since when is this problem I noticed it just now. I use Suse 8.2 and yesterday evening I made a live update. Could this be the problem? I restored the system and ... the problem remains.

I also have another linux system, there the db works fine.

Does anyone has any ideea???? Are there any settings for something like this?

Thnkx.
Andy.

I give an example:

These are the datas from the table names:

id n1 n2
1 AaAa AaAa
2 X X
3 A A
4 a a
5 ab ab
6 _Y _Y
8 ..a ..a
9 .x .x
7 ...a ...a

If I run: select * from names order by n1: the result is:

id n1 n2
4 a a
8 ..a ..a
7 ...a ...a
3 A A
1 AaAa AaAa
5 ab ab
9 .x .x
2 X X
6 _Y _Y

Which I don't think that is right. I don't know what happened. But I find that the ASCII char order is not respected here.

I have another machine on which the result is okay....I don't have a clue.

Andy.

----- Original Message -----
From: Donald Fraser
To: [ADMIN]
Sent: Tuesday, November 25, 2003 6:21 PM
Subject: Re: [ADMIN] Sensitive sort problem?

PostgreSQL 7.3.x and anything below has never had case insensitive sorting - period.
I can't speak for the new 7.4 version?

If you want to add a case insensitive data type try this:
http://gborg.postgresql.org/project/citext/projdisplay.php


Regards
Donald Fraser.
----- Original Message -----
From: Andrei Bintintan
To: pgsql-admin(at)postgresql(dot)org
Sent: Tuesday, November 25, 2003 3:04 PM
Subject: [ADMIN] Sensitive sort problem?

Hi to everyone,

I don't know exactly if i'm sending my problem in the right place, hope you can help me.

I use postgresql db. 7.3.2.

Today I noticed that my database does not sort "case sensitive" anymore. If I have a query like:
Select name from names order by name -- the names are not resulted as they should. 'ax' is after 'Aa' and so one.

I don't know since when is this problem I noticed it just now. I use Suse 8.2 and yesterday evening I made a live update. Could this be the problem? I restored the system and ... the problem remains.

I also have another linux system, there the db works fine.

Does anyone has any ideea???? Are there any settings for something like this?

Thnkx.
Andy.

----- Original Message -----
> "Donald Fraser" <demolish(at)cwgsy(dot)net> writes:
> > PostgreSQL 7.3.x and anything below has never had case insensitive sorting
=
> > - period.
>
> This is incorrect. In point of fact it will depend on the locale the
> database is running under. I think the OP's problem is he re-initdb'd
> without noting what locale setting was active.

Correction noted.
I over-looked the fact that a local setting can provide different collation
ordering.

Out of interest, which local settings will give a case-insensitive collation
order?

Regards
Donald Fraser

On Tue, 25 Nov 2003, Andrei Bintintan wrote:

> I give an example:
>
> These are the datas from the table names:
>
> id n1 n2
> 1 AaAa AaAa
> 2 X X
> 3 A A
> 4 a a
> 5 ab ab
> 6 _Y _Y
> 8 ..a ..a
> 9 .x .x
> 7 ...a ...a
>
> If I run: select * from names order by n1: the result is:
>
> id n1 n2
> 4 a a
> 8 ..a ..a
> 7 ...a ...a
> 3 A A
> 1 AaAa AaAa
> 5 ab ab
> 9 .x .x
> 2 X X
> 6 _Y _Y
>
> Which I don't think that is right. I don't know what happened. But I
> find that the ASCII char order is not respected here.

As Tom said, you've probably run initdb in a locale other than "C".
For example, I believe the above is correct for en_US.

The db was initialized for 4 months. And in rest I did nothing regarding the
initialization.

I solved the problem by delete/reinit the database. I really don't know what
caused this.
A day before I noticed the problem I made a "live update" for suse 8.2. I
will look also the log files, maybe I find there something.

If I found something "strange" I post it.

Thankx to all.

----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Andrei Bintintan" <klodoma(at)ar-sd(dot)net>
Cc: "[ADMIN]" <pgsql-admin(at)postgresql(dot)org>
Sent: Tuesday, November 25, 2003 7:53 PM
Subject: Re: [ADMIN] Sensitive sort problem?

> On Tue, 25 Nov 2003, Andrei Bintintan wrote:
>
> > I give an example:
> >
> > These are the datas from the table names:
> >
> > id n1 n2
> > 1 AaAa AaAa
> > 2 X X
> > 3 A A
> > 4 a a
> > 5 ab ab
> > 6 _Y _Y
> > 8 ..a ..a
> > 9 .x .x
> > 7 ...a ...a
> >
> > If I run: select * from names order by n1: the result is:
> >
> > id n1 n2
> > 4 a a
> > 8 ..a ..a
> > 7 ...a ...a
> > 3 A A
> > 1 AaAa AaAa
> > 5 ab ab
> > 9 .x .x
> > 2 X X
> > 6 _Y _Y
> >
> > Which I don't think that is right. I don't know what happened. But I
> > find that the ASCII char order is not respected here.
>
> As Tom said, you've probably run initdb in a locale other than "C".
> For example, I believe the above is correct for en_US.

The db was initialized for 4 months. And in rest I did nothing regarding the
initialization.

I solved the problem by delete/reinit the database. I really don't know what
caused this.
A day before I noticed the problem I made a "live update" for suse 8.2. I
will look also the log files, maybe I find there something.

If I found something "strange" I post it.

Thankx to all.

----- Original Message -----
From: "Stephan Szabo" <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Andrei Bintintan" <klodoma(at)ar-sd(dot)net>
Cc: "[ADMIN]" <pgsql-admin(at)postgresql(dot)org>
Sent: Tuesday, November 25, 2003 7:53 PM
Subject: Re: [ADMIN] Sensitive sort problem?

> On Tue, 25 Nov 2003, Andrei Bintintan wrote:
>
> > I give an example:
> >
> > These are the datas from the table names:
> >
> > id n1 n2
> > 1 AaAa AaAa
> > 2 X X
> > 3 A A
> > 4 a a
> > 5 ab ab
> > 6 _Y _Y
> > 8 ..a ..a
> > 9 .x .x
> > 7 ...a ...a
> >
> > If I run: select * from names order by n1: the result is:
> >
> > id n1 n2
> > 4 a a
> > 8 ..a ..a
> > 7 ...a ...a
> > 3 A A
> > 1 AaAa AaAa
> > 5 ab ab
> > 9 .x .x
> > 2 X X
> > 6 _Y _Y
> >
> > Which I don't think that is right. I don't know what happened. But I
> > find that the ASCII char order is not respected here.
>
> As Tom said, you've probably run initdb in a locale other than "C".
> For example, I believe the above is correct for en_US.