1

I need to create a unique constraint on citext column, but it fails. Here is the example:

create table test (name citext);

ALTER TABLE ONLY test
    ADD CONSTRAINT test_name_uq UNIQUE (name);

  insert into test(name) values ('SIMIT'); --succeded.

  insert into test(name) values ('SİMİT'); --failed

Here is the error code:

ERROR: duplicate key value violates unique constraint "test_name_uq" DETAIL: Key (name)=(SİMİT) already exists. SQL state: 23505

SIMIT and SİMİT is not the same thing. It should not return an error. I don't want to add any upper or lower function in my code. Is there any suggestion?

  • What are the lc_ctype of the database and the operating system? – Daniel Vérité Sep 18 '18 at 13:15
  • When I run "SHOW client_encoding;" it returns "'UNICODE" , my database is Amazon RDS Postgresql, that's why I don't know my OS. – Banu Akkus Sep 18 '18 at 14:23
  • show lc_ctype ? – Daniel Vérité Sep 18 '18 at 14:42
  • It returns error 'ERROR: unrecognized configuration parameter "lc_type" SQL state: 42704' – Banu Akkus Sep 18 '18 at 14:49
  • It's not lc_type but lc_ctype. This is what drives the behavior of lower() and by extension citext. – Daniel Vérité Sep 18 '18 at 15:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.