As Zdenek has already said, PostgreSQL on Solaris uses the operating
system locales and their collation rules. These locale definitions on
Solaris are based on international standards. PostgreSQL cannot change
them, only use them.
To do what you require, you need to use a locale that orders upper and
lower case characters together.
Actually, most of the European language locales do this! So I have to
assume you created your current database with the default C locale. e.g.
$ initdb
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 C.*
.... snip ...
$ psql
postgres=# show lc_collate;
lc_collate
------------
C
(1 row)
postgres=# select * from blob order by barf;
barf
------
A
B
C
a
b
c
(6 rows)
You should run initdb specifying --locale= to whatever the appropriate
language is. e.g.
$ initdb --locale=en_GB.ISO8859-15
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 en_GB.ISO8859-15.*
*The default database encoding has accordingly been set to LATIN9.*
.... snip ...
$ psql
postgres=# show lc_collate;
lc_collate
------------------
en_GB.ISO8859-15
(1 row)
postgres=# select * from blob order by barf;
barf
------
a
A
b
B
c
C
(6 rows)
NOTE. The operating system needs to have the necessary packages
installed to support the language/locale you choose. e.g.
$ locale -a
C
POSIX
en_GB
en_GB.ISO8859-1
en_GB.ISO8859-15
en_GB.ISO8859-15 at euro
en_GB.UTF-8
en_IE
en_IE.ISO8859-1
en_IE.ISO8859-15
en_IE.ISO8859-15 at euro
en_IE.UTF-8
fr.ISO8859-15
fr.UTF-8
fr_BE
fr_BE.ISO8859-1
fr_BE.ISO8859-15
fr_BE.ISO8859-15 at euro
fr_BE.UTF-8
fr_FR.ISO8859-15
fr_FR.ISO8859-15 at euro
fr_FR.UTF-8
fr_LU.UTF-8
iso_8859_1
nl
nl.ISO8859-15
nl_BE
nl_BE.ISO8859-1
nl_BE.ISO8859-15
nl_BE.ISO8859-15 at euro
nl_BE.UTF-8
nl_NL
nl_NL.ISO8859-1
nl_NL.ISO8859-15
nl_NL.ISO8859-15 at euro
nl_NL.UTF-8
Anubha Khurana wrote:
> Thanks for your replies.
>
> Probably I didn't explain my problem well. I am using a third party tool
> which I'll be installing on Solaris machine, and that tool is using
> Postgres as the database. The queries are hardcoded in it and I can not
> change them. So right now when I do 'initdb' without specifying
> '-locale' or '-lc-collate', I see that the 'order by' clause returns me
> 'case sensitive' search.
>
> And therefore the third party tool also returns me case sensitive results.
>
> I need postgres to be installed in a way so that 'order by' clause
> itself returns me 'case-insensitive' search and I don't have to use
> upper() or lower() functions in the query.
>
> Thanks for all your help
> Regards
> Anubha
>
> ----- Original Message ----
> From: Zdenek Kotala <Zdenek.Kotala at Sun.COM>
> To: Anubha <anubhakhurana at yahoo.com>
> Cc: opensolaris-discuss at opensolaris.org
> Sent: Tuesday, October 7, 2008 1:03:04 AM
> Subject: Re: [osol-discuss] Installing postgres on Solaris with case
> insensitive 'order by' clause
>
> Anubha napsal(a):
> > Actually this is more a postgres question than a Solaris Ques? I am
> too new to both Solaris and postgres, so please bear with me.
> >
> > I am installing postgres on Solaris. For that I am using initdb.
> >
> > My Objective: I want all queries that execute with ?ORDER BY?
> clause, to be case-insensitive. So that, while sorting, the small ?a?
> and capital ?A?, are together.
> > My Assumption: Some value for locale or LC_COLLATE in initdb command
> will help me do this
> > My Problem: What should be that value? The locale value that I need
> to mention with initdb is what, creating problem. So I need to have a
> locale value for, I guess, LC_COLLATE which will help me do this. This
> is my guess.
>
> PostgreSQL uses system locales and collations. If you interesting to
> have case insensitive sorting you need to do something like "ORDER BY
> uppercase(mycol)" or you can look on http://pgfoundry.org/projects/citext/
>
> Zdenek
>
> PS: Next time you should use databases-discuss at opensolaris.org
> <mailto:databases-discuss at opensolaris.org>
>
>
> Zdenek
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> databases-discuss mailing list
> databases-discuss at opensolaris.org
> http://mail.opensolaris.org/mailman/listinfo/databases-discuss
--
Jim Gates Sun Microsystems
Nashua, NH, USA http://sun.com/postgresql
