diff options
Diffstat (limited to 'contrib/btree_gist')
| -rw-r--r-- | contrib/btree_gist/expected/without_overlaps.out | 48 | ||||
| -rw-r--r-- | contrib/btree_gist/sql/without_overlaps.sql | 28 |
2 files changed, 76 insertions, 0 deletions
diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out index be52c522e89..18856900ded 100644 --- a/contrib/btree_gist/expected/without_overlaps.out +++ b/contrib/btree_gist/expected/without_overlaps.out @@ -42,3 +42,51 @@ INSERT INTO temporal_rng VALUES (1, '[2000-06-01,2001-01-01)'); ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" DETAIL: Key (id, valid_at)=(1, [06-01-2000,01-01-2001)) conflicts with existing key (id, valid_at)=(1, [01-01-2000,01-01-2001)). +-- Foreign key +CREATE TABLE temporal_fk_rng2rng ( + id integer, + valid_at daterange, + parent_id integer, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +\d temporal_fk_rng2rng + Table "public.temporal_fk_rng2rng" + Column | Type | Collation | Nullable | Default +-----------+-----------+-----------+----------+--------- + id | integer | | not null | + valid_at | daterange | | not null | + parent_id | integer | | | +Indexes: + "temporal_fk_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +Foreign-key constraints: + "temporal_fk_rng2rng_fk" FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) + +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + pg_get_constraintdef +--------------------------------------------------------------------------------------- + FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) +(1 row) + +-- okay +INSERT INTO temporal_fk_rng2rng VALUES + (1, '[2000-01-01,2001-01-01)', 1); +-- okay spanning two parent records: +INSERT INTO temporal_fk_rng2rng VALUES + (2, '[2000-01-01,2002-01-01)', 1); +-- key is missing +INSERT INTO temporal_fk_rng2rng VALUES + (3, '[2000-01-01,2001-01-01)', 3); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(3, [01-01-2000,01-01-2001)) is not present in table "temporal_rng". +-- key exist but is outside range +INSERT INTO temporal_fk_rng2rng VALUES + (4, '[2001-01-01,2002-01-01)', 2); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(2, [01-01-2001,01-01-2002)) is not present in table "temporal_rng". +-- key exist but is partly outside range +INSERT INTO temporal_fk_rng2rng VALUES + (5, '[2000-01-01,2002-01-01)', 2); +ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk" +DETAIL: Key (parent_id, valid_at)=(2, [01-01-2000,01-01-2002)) is not present in table "temporal_rng". diff --git a/contrib/btree_gist/sql/without_overlaps.sql b/contrib/btree_gist/sql/without_overlaps.sql index 433c609ab22..b1b581fcabc 100644 --- a/contrib/btree_gist/sql/without_overlaps.sql +++ b/contrib/btree_gist/sql/without_overlaps.sql @@ -23,3 +23,31 @@ INSERT INTO temporal_rng VALUES -- should fail: INSERT INTO temporal_rng VALUES (1, '[2000-06-01,2001-01-01)'); + +-- Foreign key +CREATE TABLE temporal_fk_rng2rng ( + id integer, + valid_at daterange, + parent_id integer, + CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), + CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) + REFERENCES temporal_rng (id, PERIOD valid_at) +); +\d temporal_fk_rng2rng +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rng_fk'; + +-- okay +INSERT INTO temporal_fk_rng2rng VALUES + (1, '[2000-01-01,2001-01-01)', 1); +-- okay spanning two parent records: +INSERT INTO temporal_fk_rng2rng VALUES + (2, '[2000-01-01,2002-01-01)', 1); +-- key is missing +INSERT INTO temporal_fk_rng2rng VALUES + (3, '[2000-01-01,2001-01-01)', 3); +-- key exist but is outside range +INSERT INTO temporal_fk_rng2rng VALUES + (4, '[2001-01-01,2002-01-01)', 2); +-- key exist but is partly outside range +INSERT INTO temporal_fk_rng2rng VALUES + (5, '[2000-01-01,2002-01-01)', 2); |