Обсуждение: case sensitive/insensitive confusion

I am seeing different ORDER BY results 
on a character column on different machines. 
I have (1)
ResyDBE=# select version();                               version
------------------------------------------------------------------------PostgreSQL 7.4.5 on hppa-hp-hpux10.20, compiled
byGCC gcc (GCC) 3.3.1
 

ResyDBE=# \l ResyDBE      List of databases  Name    | Owner | Encoding
-----------+-------+-----------ResyDBE   | rodos | SQL_ASCII

ResyDBE=# select parameter_name from parameter_define where
parameter_name ilike 'c%' order by 1 limit 8;parameter_name
----------------CAENAMCAENAMCAENAMCBERRYCBERRYCCCTOGCCCTOGCCCTOG

and (2)
ResyDBE=# select version();                                     
version                                       
-------------------------------------------------------------------------------------PostgreSQL 7.4.5 on
i686-pc-linux-gnu,compiled by GCC gcc (GCC) 3.3.1
 
(SuSE Linux)

ResyDBE=# \l      List of databases  Name    | Owner | Encoding  
-----------+-------+-----------ResyDBE   | rodos | SQL_ASCII

ResyDBE=# select parameter_name from parameter_define where
parameter_name ilike 'c%' order by 1 limit 8;parameter_name 
----------------c12a        c12b        c12c        c1a         CAENAM      CAENAM      CAENAM      CAENAM      

It seems to me under hpux the sort is done case sensitive, 
as would one expect on SQL_ASCII encoding, whereas 
under linux a case insensitive sort is done. 
I'd like to see the hpux behaviour under linux too. 
Has anybody seen something like this? 
TIA

Regards, Christoph

Christoph Haller wrote:
> It seems to me under hpux the sort is done case sensitive,
> as would one expect on SQL_ASCII encoding, whereas
> under linux a case insensitive sort is done.

The sort order depends entirely on the locale that you specify to initdb 
(not the encoding).  Please check the documentation there.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:
> 
> Christoph Haller wrote:
> > It seems to me under hpux the sort is done case sensitive,
> > as would one expect on SQL_ASCII encoding, whereas
> > under linux a case insensitive sort is done.
> 
> The sort order depends entirely on the locale that you specify to initdb
> (not the encoding).  Please check the documentation there.
> 
> --
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
> 

Under hpux I see
pg_controldata $PGDATA
...
LC_COLLATE:                           C
LC_CTYPE:                             C
Under linux I see
pg_controldata $PGDATA
...
LC_COLLATE:                           en_US
LC_CTYPE:                             en_US

That explains the confusion, even to me. 
Thanx to Peter for the quick reply. 

Regards, Christoph

I seem to have a problem with controlling the locale.

Mac os x, postgresql 8.0.1

./configure --with-rendezvous --enable-thread-safety
--enable-locale

but when I try:

initdb --locale=es_ES ~/testdb

I get:  

The files belonging to this database system will be
owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale
es_ES.
initdb: could not find suitable encoding for locale
"es_ES"
Rerun initdb with the -E option.
Try "initdb --help" for more information.

Am I missing something in OS X, or is this a
postgresql issue, or a combination?

I even tried

initdb --locale=en_US ~/testdb

and I still get the same error....

I am actually looking based on a conversation with
someone in Portugal that can not figure it out and he
needs it.

Any help to pointers would be great...

Ted

--- Peter Eisentraut <peter_e@gmx.net> wrote:

> Christoph Haller wrote:
> > It seems to me under hpux the sort is done case
> sensitive,
> > as would one expect on SQL_ASCII encoding, whereas
> > under linux a case insensitive sort is done.
> 
> The sort order depends entirely on the locale that
> you specify to initdb 
> (not the encoding).  Please check the documentation
> there.
> 
> -- 
> Peter Eisentraut
> http://developer.postgresql.org/~petere/
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
>       joining column's datatypes do not match
> 

    
__________________________________ 
Do you Yahoo!? 
Take Yahoo! Mail with you! Get it on your mobile phone. 
http://mobile.yahoo.com/maildemo 

Theodore Petrosky <tedpet5@yahoo.com> writes:
> Mac os x, postgresql 8.0.1
> initdb --locale=es_ES ~/testdb
> ...
> The database cluster will be initialized with locale es_ES.
> initdb: could not find suitable encoding for locale "es_ES"
> Rerun initdb with the -E option.

I looked into this and find that the issue seems to be Apple's rather
limited (not to say brain dead) locale support.  What I see on OSX
10.3.7 is that <langinfo.h> defines CODESET, but nl_langinfo(CODESET)
always returns an empty string.  So there's not any obvious way to
determine the correct encoding associated with a locale setting.
        regards, tom lane