case insensitive Foreign Key PostgreSQL

Question

Need to know if I can add a case insensitive foreign key constraint to a table in PostgreSQL. I am converting a DB from MySQL to PostgreSQL. The foreign keys were already created in MySQL and I did not face any problems because MySQL was not case sensitive. When I load all the data from MySQL to PostgreSQL and then try to add foreign keys to the tables, I get an error in Postgres. For example:

Table A had 2 columns: ID (int) and Name(varchar(25))

Entry1: ID = 1 , Name = 'America' Entry2: ID = 2 , Name = 'Canada'

Table B had 2 columns: ID (int) and Name(varchar(25))

Entry1: ID(int) = 10 , Name(Varchar(25)) = 'AmeRiCA' Entry1: ID = 1 , Name = 'Canada'

in MySQL, the foreign key was created between Table A and Table B on column "Name", however in Postgres, because of the case sensitive nature I get an error.

I do not have an option to Alter the table and change Varchar to citext. is there anyway I can define a case insensitive foreign key in PG.

Any suggestions?

Thanks

Your question is interesting but I'm curious... Why would you prefer to use the name as key (to create the fk) instead of the id ? — Jorge Campos
– Jorge Campos, Commented Oct 15, 2014 at 17:48
@JorgeCampos -- Well thats the same question which came to my mind when I started looking at this DB. This DB was created already and handed over to me. So, do you think there is anyway to accomplish this task? — WinSupp
– WinSupp, Commented Oct 15, 2014 at 18:07
I think that without the citext option you would have to create a trigger before insert/update/delete to check if that rows exists using lower or upper functions. I know, its uggly! — Jorge Campos
– Jorge Campos, Commented Oct 15, 2014 at 18:16

Tometzky · Accepted Answer · 2014-10-15 22:00:00Z

2

By far the best thing you can do is to correct your data. For example like this:

update A set name=initcap(name) where name<>initcap(name);
update B set name=initcap(name) where name<>initcap(name);

If it's unacceptable then you can create additional column in both tables, say name_lower, which would be automatically set to lower(name) using a trigger:

create or replace function name_lower_trigger() returns trigger as $$
  begin
    NEW.name_lower=lower(name);
    return NEW;
  end;
$$ language plpgsql;

create trigger a_name_lower_trigger
  before insert or update on a
  for each row execute procedure name_lower_trigger();
create trigger b_name_lower_trigger
  before insert or update on b
  for each row execute procedure name_lower_trigger();

And create a foreign key constraint using these columns. But it would denormalize a database and waste storage and is ugly. It's much better to correct your data.

answered Oct 15, 2014 at 22:00

Tometzky

24.2k5 gold badges64 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Richard Huxton Over a year ago

This is pretty much the only sensible solution. The only other alternative would be to implement your own foreign-key triggers doing a forced-lowercase comparison or some such.

Collectives™ on Stack Overflow

case insensitive Foreign Key PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related