Re: Temporal foreign keys

On Fri, 2012-03-16 at 15:13 +0100, Andreas Kretschmer wrote:
> > On Fri, 2012-02-03 at 07:58 +0100, Matthias wrote:
> > > how can I implement temporal foreign keys with postgresql? Is writing
> > > triggers the only way to enforce temporal referential integrity
> > > currently?
> >
> It works in 9.2devel ;-)
>
> test=# create table x (d daterange primary key);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey"
> for table "x"
> CREATE TABLE
> test=*# create table y (d daterange references x);
> CREATE TABLE
> test=*# insert into x values ('[2012-01-01,2012-01-10)');
> INSERT 0 1
> test=*# insert into y values ('[2012-01-01,2012-01-10)');
> INSERT 0 1
> test=*# insert into y values ('[2012-01-01,2012-01-20)');
> ERROR: insert or update on table "y" violates foreign key constraint "y_d_fkey"
> DETAIL: Key (d)=([2012-01-01,2012-01-20)) is not present in table "x".

If I understand what he was asking for, it was a kind of "range foreign
key" which means that the following query should succeed:

insert into y values ('[2012-01-02,2012-01-04)');

because that range is contained in a value in the table x.

So it's slightly different semantics than a normal foreign key.

But yes, normal foreign keys (based on equality) work fine over range
types.

Regards,
Jeff Davis