pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcase sensitive/insensitive confusion
5+ messages / 4 participants
[nested] [flat]
* case sensitive/insensitive confusion
@ 2005-02-01 09:50 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 5+ messages in thread
From: Christoph Haller @ 2005-02-01 09:50 UTC (permalink / raw)
To: pgsql-sql
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 by GCC 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
----------------
CAENAM
CAENAM
CAENAM
CBERRY
CBERRY
CCCTOG
CCCTOG
CCCTOG
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: case sensitive/insensitive confusion
@ 2005-02-01 10:14 Peter Eisentraut <peter_e@gmx.net>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 2 replies; 5+ messages in thread
From: Peter Eisentraut @ 2005-02-01 10:14 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: case sensitive/insensitive confusion
@ 2005-02-01 11:44 Christoph Haller <ch@rodos.fzk.de>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 0 replies; 5+ messages in thread
From: Christoph Haller @ 2005-02-01 11:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: case sensitive/insensitive confusion
@ 2005-02-01 12:48 Theodore Petrosky <tedpet5@yahoo.com>
parent: Peter Eisentraut <peter_e@gmx.net>
1 sibling, 1 reply; 5+ messages in thread
From: Theodore Petrosky @ 2005-02-01 12:48 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: case sensitive/insensitive confusion
@ 2005-02-01 18:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Theodore Petrosky <tedpet5@yahoo.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-02-01 18:36 UTC (permalink / raw)
To: Theodore Petrosky <tedpet5@yahoo.com>; +Cc: pgsql-sql@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-02-01 18:36 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-01 09:50 case sensitive/insensitive confusion Christoph Haller <ch@rodos.fzk.de>
2005-02-01 10:14 ` Peter Eisentraut <peter_e@gmx.net>
2005-02-01 11:44 ` Christoph Haller <ch@rodos.fzk.de>
2005-02-01 12:48 ` Theodore Petrosky <tedpet5@yahoo.com>
2005-02-01 18:36 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox