Thread: Case Insensitive Foreign Key Constraint

Case Insensitive Foreign Key Constraint

From
"George Weaver"
Date:
Hi all,
 
Is there a way to have text-type foreign keys be case insensitive?
 
development=# CREATE TABLE foo (foo text PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE

development=# INSERT INTO foo VALUES ('foo');
INSERT 0 1
development=# CREATE TABLE foo1 (foo text REFERENCES foo);
CREATE TABLE

development=# INSERT INTO foo1 VALUES ('FOO');
ERROR:  insert or update on table "foo1" violates foreign key constraint "foo1_foo_fkey"
DETAIL:  Key (foo)=(FOO) is not present in table "foo".
 
Thanks,
George

Re: Case Insensitive Foreign Key Constraint

From
Peter Geoghegan
Date:
I would probably just have a check constraint that prevented the
relevant PK field from being lower case in the first place. I had to
do that recently, but my approach reflected the business rules.

You may prefer to use citext:

http://www.postgresql.org/docs/current/interactive/citext.html

--
Regards,
Peter Geoghegan

Re: Case Insensitive Foreign Key Constraint

From
"George Weaver"
Date:
>From: Peter Geoghegan
>Subject: Re: [GENERAL] Case Insensitive Foreign Key Constraint

>I would probably just have a check constraint that prevented the
>relevant PK field from being lower case in the first place. I had to
>do that recently, but my approach reflected the business rules.

This is what I've been looking at doing.  My need reflects a customer's
business rules as well.

>You may prefer to use citext:>

http://www.postgresql.org/docs/current/interactive/citext.html

Interesting - good long term solution!

Thanks Peter.

>--
>Regards,
>Peter Geoghegan