CODEWITHSUNDEEP
sqlpostgresqlcase-sensitivepostgresql-9.3
WinSupp
WinSupp

Reputation: 371

case insensitive Foreign Key PostgreSQL

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

Upvotes: 4

Views: 2756

Answers (1)

Tometzky
Tometzky

Reputation: 23920

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.

Upvotes: 2

Related Questions