Thread: collation
Hi! I have been looking all around, but I don't see any support for collation in pgsql - are there other ways of performing cases-insensitive searches? Cheers, Jens
Date sent: Sun, 26 Jan 2003 08:13:56 +0100
Subject: [NOVICE] collation
From: Jens Østergaard Petersen <oesterg@hum.ku.dk>
To: pgsql-novice@postgresql.org
Would this do the trick?
select x from y where upper(x) =upper('TestString');
Hope this helps
Paul Butler
Hi!
I have been looking all around, but I don't see any support for
collation in pgsql - are there other ways of performing
cases-insensitive searches?
Cheers,
Jens
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Thank for the tips, Oliver and Paul! I take it then that there are no means of constructing collation tables (like I do in FrontBase), that allow me to control which characters are equalled with which characters in searches - this is a part of SQL92, I believe. Jens On Sunday, Jan 26, 2003, at 13:54 Europe/Copenhagen, Oliver Elphick wrote: > On Sun, 2003-01-26 at 07:13, Jens Østergaard Petersen wrote: >> Hi! >> >> I have been looking all around, but I don't see any support for >> collation in pgsql - are there other ways of performing >> cases-insensitive searches? > > You can use ILIKE or you can use a regular expression with the ~* > operator. > > -- > Oliver Elphick Oliver.Elphick@lfix.co.uk > Isle of Wight, UK > http://www.lfix.co.uk/oliver > GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C > ======================================== > "Wash me thoroughly from mine iniquity, and cleanse me > from my sin. For I acknowledge my transgressions; and > my sin is ever before me. Against thee, thee only, > have I sinned, and done this evil in thy sight..." > Psalms 51:2-4 >
=?ISO-8859-1?Q?Jens_=D8stergaard_Petersen?= <oesterg@hum.ku.dk> writes:
> Thank for the tips, Oliver and Paul! I take it then that there are no
> means of constructing collation tables (like I do in FrontBase), that
> allow me to control which characters are equalled with which characters
> in searches - this is a part of SQL92, I believe.
I'm not sure whether SQL92 allows for user-defined collations, but in
any case we don't yet have any of the SQL-specified facilities in that
area. Postgres just sits atop the libc locale facilities (strcoll()
etc). If you do not like any of the locales available on your machine
then you need to provide your own locale definition. I've never done
it, so I can't say what degree of wizardry is needed... but it's
certainly possible.
BTW, if you do find yourself issuing lots of queries like
select * from ... where lower(col) = lower('foo');
then you'll want to create a functional index on lower(col) to
make this go faster.
regards, tom lane
I'm going to be dealing with collations and locales not provided by anyone.
It sounds like I can't create my own collations. Is that true? If so, what
do I have to do to create my own locales?
Steve Miller
On 01/26/2003 12:49:15 PM pgsql-novice-owner wrote:
>=?ISO-8859-1?Q?Jens_=D8stergaard_Petersen?= <oesterg@hum.ku.dk> writes:
>> Thank for the tips, Oliver and Paul! I take it then that there are no
>> means of constructing collation tables (like I do in FrontBase), that
>> allow me to control which characters are equalled with which characters
>> in searches - this is a part of SQL92, I believe.
>
>I'm not sure whether SQL92 allows for user-defined collations, but in
>any case we don't yet have any of the SQL-specified facilities in that
>area. Postgres just sits atop the libc locale facilities (strcoll()
>etc). If you do not like any of the locales available on your machine
>then you need to provide your own locale definition. I've never done
>it, so I can't say what degree of wizardry is needed... but it's
>certainly possible.
>
>BTW, if you do find yourself issuing lots of queries like
> select * from ... where lower(col) = lower('foo');
>then you'll want to create a functional index on lower(col) to
>make this go faster.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
Steve_Miller@sil.org writes:
> I'm going to be dealing with collations and locales not provided by anyone.
> It sounds like I can't create my own collations. Is that true?
AFAIK, you define collation as part of a locale.
> If so, what do I have to do to create my own locales?
Can't say, never did it.
regards, tom lane
>AFAIK, you define collation as part of a locale. Fair enough. But a collation can be used for more than one locale, and a locale can have more than one location. And I'm going to need to know how to create both. Maybe I should submit it to the general list? Steve Miller On 01/27/2003 03:40:38 PM pgsql-novice-owner wrote: >Steve_Miller@sil.org writes: >> I'm going to be dealing with collations and locales not provided by anyone. >> It sounds like I can't create my own collations. Is that true? > >AFAIK, you define collation as part of a locale. > >> If so, what do I have to do to create my own locales? > >Can't say, never did it. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/users-lounge/docs/faq.html
Steve Miller writes: > I'm going to be dealing with collations and locales not provided by anyone. > It sounds like I can't create my own collations. Is that true? If so, what > do I have to do to create my own locales? Yes -- but the advantage is, once you have done so your OS tools can use the locales as well, not just PostgreSQL. I don't recall your mentioning what operating system you are using, but I suspect you want to check out localedef(1). If that command isn't known to your OS try checking the "SEE ALSO" section of the setlocale(3) manual page for references. It's over a decade since I built a locale so I won't offer further advice, except to say that it wasn't especially hard even that long ago. :-) Regards, Giles
Giles, Thanks for the response. >> I'm going to be dealing with collations and locales not provided by anyone. >> It sounds like I can't create my own collations. Is that true? If so, what >> do I have to do to create my own locales? > >Yes -- but the advantage is, once you have done so your OS tools can >use the locales as well, not just PostgreSQL. > >I don't recall your mentioning what operating system you are using, >but I suspect you want to check out localedef(1). If that command >isn't known to your OS try checking the "SEE ALSO" section of the >setlocale(3) manual page for references. We are working on a port from Windows and MS SQL Server to Linux. Postgresql is the first database we're trying. If the Linux port works well enough, we may even replace SQL Server with Postgresql in Windows someday. The idea that the locale will be available to the OS is quite intriguing. I'm assuming you're talking about Linux. We're spending months of time finding a workaround for custom locales in Windows. >It's over a decade since I built a locale so I won't offer further >advice, except to say that it wasn't especially hard even that long >ago. :-) That's very encouraging. Steve Miller
Steve Miller writes:
> The idea that the locale will be available to the OS is quite intriguing.
> I'm assuming you're talking about Linux. We're spending months of time
> finding a workaround for custom locales in Windows.
setlocale() and localedef should be in any Unix-like OS with locale
support, including Linux. I don't know when they were standardised
but they're certainly in the "Single Unix Standard, version 2" aka
UNIX98:
http://www.opengroup.org/onlinepubs/007908799/xcu/localedef.html
http://www.opengroup.org/onlinepubs/007908799/xsh/setlocale.html
setlocale() is in ISO C (aka ANSI C) too. localedef probably isn't,
since it is an operating system utility.
I can't help you with Windows, I'm afraid, but good luck.
Regards,
Giles