Thread: 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