Обсуждение: postgresql database use a case insensitive collation
Hi,
Dennis
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
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
Вложения
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
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 (seeCREATE 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