Foreign key on partial char field

[lmanorders]

I'm using Postgres 9.2 and connecting to it using libpq.dll on Windows.

I have two tables:

A zip code table with zcode char(5) Primary key, city varchar(30), and state char(2).

 

An employee table that, among other things, contains a zip code field. This field can hold a nine-digit zip code.

I want to add a constraint (foreign key) on the first 5 digits of this zip code that references the zip code table.

I've tried several things, including the following, but I get an error when I try to create the table: 

...

zcode char(9)

CONSTRAINT employee_zipcode_fkey FOREIGN KEY (SUBSTRING(zipcode from 1 for 5))
     REFERENCES zipcds (zipcode) ON UPDATE CASCADE ON DELETE RESTRICT

...

 

Can anyone tell me if this is possible, and if it is, how can this be done?

 

Thanks in advance,

Lynn

 

[Merlin Moncure]

It's not directly possible. FK references take only raw fields, not
expressions. I would probably consider breaking out the zipcode into
a seprate field, and using a 'before' trigger to populate (taking care
it fires both on insert/update). Also consider use of left() vs the
stupidly verbose substring().

merlin


--
Sent via pgsql-novice mailing list (pgsql-...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice