0

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.

SwissNavy
  • 619
  • 1
  • 12
  • 28
  • Does this answer your question? [How do you change the character encoding of a postgres database?](https://stackoverflow.com/questions/5090858/how-do-you-change-the-character-encoding-of-a-postgres-database) – ESG Aug 07 '20 at 13:30
  • No. First, I am not changing encoding, I am changing case-insensitive collation. Second, the answer suggests dropping and recreating the db. – SwissNavy Aug 07 '20 at 13:53
  • There is no alter database statement that allows to change the default collation. You can alter each column 1 by one, but you'd also need to include the collation when creating new columns/tables. Recreating is still the better solution. – ESG Aug 07 '20 at 13:59
  • And if I don't mind to ALTER columns one by one, how do I do that? – SwissNavy Aug 07 '20 at 14:03

1 Answers1

2

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';
Belayer
  • 13,578
  • 2
  • 11
  • 22