CODEWITHSUNDEEP
databasepostgresqlcollationcase-insensitive
SwissNavy
SwissNavy

Reputation: 649

postgresql 12.3 convert existing DB to use case-insensitive collation?

postgresql 12.3 in Liux container (Alpine 3.10)

I would like to convert an existing DB to use case-insensitive collation. Is it possible? I mean, without dropping the database and recreating from scratch.

Also: if it's possible to do at a table or a field level, that's also fine, but again, without dropping and recreating the table.

Upvotes: 0

Views: 1237

Answers (1)

Belayer
Belayer

Reputation: 14934

You could change to a nondeterministic collation, but since that seems to not be available you can install the citext extension, then alter your columns to type citext.

create extension if not exists citext. 
alter table *table_name* alter column *column_name* set data type citext';

Full example follows. I attempted to create a fiddle. However, citext did not exist and I did not have authority to install the extension.

create extension if not exists citext; 

create table test_ci( col1 text, col2 citext);
insert into test_ci(col1,col2)
   ( values ('ABC','XyZ'), ('ABc','lll'), ('Abc','LLL'), ('abc','xYz'))
   
select *
  from test_ci 
 where col1 = 'ABC';

select *
  from test_ci 
 where col2 = 'XYZ';

alter table test_ci alter col1 set data type citext;
select *
  from test_ci 
 where col1 = 'ABC';

Upvotes: 3

Related Questions