pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpostgresql database use a case insensitive collation
5+ messages / 5 participants
[nested] [flat]
* postgresql database use a case insensitive collation
@ 2022-05-25 06:19 Dennis <daslaner@gmail.com>
0 siblings, 4 replies; 5+ messages in thread
From: Dennis @ 2022-05-25 06:19 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi,
How to make configuration my postgresql database use a case insensitive
collation?
Dennis
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgresql database use a case insensitive collation
@ 2022-05-25 06:39 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Dennis <daslaner@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2022-05-25 06:39 UTC (permalink / raw)
To: Dennis <daslaner@gmail.com>; pgsql-admin@lists.postgresql.org
On Wed, 2022-05-25 at 09:19 +0300, Dennis wrote:
> How to make configuration my postgresql database use a case insensitive collation?
You'd have to define such a collation with CREATE COLLATION and you have to use it
in column definitions explicitly, since such a collation cannot be used as database
collation.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgresql database use a case insensitive collation
@ 2022-05-25 07:33 Holger Jakobs <holger@jakobs.com>
parent: Dennis <daslaner@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Holger Jakobs @ 2022-05-25 07:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Am 25.05.22 um 08:19 schrieb Dennis:
> Hi,
>
> How to make configuration my postgresql database use a case
> insensitive collation?
>
> Dennis
Hi Dennis,
you may use the data type citext (case insensitive text). It's not
builtin, but an extension, which has to be activated in every database with
CREATE EXTENSION citext;
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
Attachments:
OpenPGP_signature (application/pgp-signature, 202B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgresql database use a case insensitive collation
@ 2022-05-25 08:43 Paul Smith <paul@pscs.co.uk>
parent: Dennis <daslaner@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Paul Smith @ 2022-05-25 08:43 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
On 25/05/2022 07:19, Dennis wrote:
> How to make configuration my postgresql database use a case
> insensitive collation?
>
Someone will probably tell me that it's not perfect, but I just use
UPPER() when searching, and create indexes using UPPER() on the fields
where I want case insensitivity.
eg:
create table books (title varchar, author varchar);
create index books_title on books (upper(title));
then
select * from books where upper(title) = upper('Return Of The Fellowship');
or
select * from books where author = 'J R R Tolkien' order by upper(title);
(These both use the 'books_title' index)
From experience, it's a lot easier to use case-insensitive search/order
when the database is case sensitive than it is the other way around!
Paul
--
Paul Smith Computer Services
Tel: 01484 855800
Vat No: GB 685 6987 53
Sign up for news & updates at http://www.pscs.co.uk/go/subscribe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: postgresql database use a case insensitive collation
@ 2022-05-26 14:38 jian he <jian.universality@gmail.com>
parent: Dennis <daslaner@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: jian he @ 2022-05-26 14:38 UTC (permalink / raw)
To: Dennis <daslaner@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
Hi,
You cannot set database-wise case-insensitive collation. Since case
insensitiveness is nondeterministic comparisons.
CREATE DATABASE ma page pg 15 quote
> There is currently no option to use a database locale with
> nondeterministic comparisons (see CREATE COLLATION for an explanation).
> If this is needed, then per-column collations would need to be used.
>
You can also check: https://www.postgresql.org/docs/15/collation.html
(section 24.2.2.4)
On Wed, May 25, 2022 at 11:49 AM Dennis <daslaner@gmail.com> wrote:
> Hi,
>
> How to make configuration my postgresql database use a case insensitive
> collation?
>
> Dennis
>
--
I recommend David Deutsch's <<The Beginning of Infinity>>
Jian
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-05-26 14:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-25 06:19 postgresql database use a case insensitive collation Dennis <daslaner@gmail.com>
2022-05-25 06:39 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-05-25 07:33 ` Holger Jakobs <holger@jakobs.com>
2022-05-25 08:43 ` Paul Smith <paul@pscs.co.uk>
2022-05-26 14:38 ` jian he <jian.universality@gmail.com>
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