Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sensitive sort problem?

Sensitive sort problem?

From
"Andrei Bintintan"
Date:
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.
 

Re: Sensitive sort problem?

From
"Donald Fraser"
Date:
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:
 
 
Regards
Donald Fraser.
----- Original Message -----
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.
 

Re: Sensitive sort problem?

From
Tom Lane
Date:
"Donald Fraser" <demolish@cwgsy.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

Re: Sensitive sort problem?

From
"Andrei Bintintan"
Date:
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 -----
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:
 
 
Regards
Donald Fraser.
----- Original Message -----
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.
 

Re: Sensitive sort problem?

From
"Donald Fraser"
Date:
----- Original Message -----
> "Donald Fraser" <demolish@cwgsy.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

Re: Sensitive sort problem?

From
Stephan Szabo
Date:
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.

Re: Sensitive sort problem?

From
"Andrei Bintintan"
Date:
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@megazone.bigpanda.com>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: "[ADMIN]" <pgsql-admin@postgresql.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.

Re: Sensitive sort problem?

From
"Andrei Bintintan"
Date:
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 -----
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:
 
 
Regards
Donald Fraser.
----- Original Message -----
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.
 

Re: Sensitive sort problem?

From
"Andrei Bintintan"
Date:
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@megazone.bigpanda.com>
To: "Andrei Bintintan" <klodoma@ar-sd.net>
Cc: "[ADMIN]" <pgsql-admin@postgresql.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.