1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
|
-- Core must test WITHOUT OVERLAPS
-- with an int4range + daterange,
-- so here we do some simple tests
-- to make sure int + daterange works too,
-- since that is the expected use-case.
CREATE TABLE temporal_rng (
id integer,
valid_at daterange,
CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
);
\d temporal_rng
Table "public.temporal_rng"
Column | Type | Collation | Nullable | Default
----------+-----------+-----------+----------+---------
id | integer | | not null |
valid_at | daterange | | not null |
Indexes:
"temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
pg_get_constraintdef
---------------------------------------------
PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
(1 row)
SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
pg_get_indexdef
-------------------------------------------------------------------------------
CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at)
(1 row)
INSERT INTO temporal_rng VALUES
(1, '[2000-01-01,2001-01-01)');
-- same key, doesn't overlap:
INSERT INTO temporal_rng VALUES
(1, '[2001-01-01,2002-01-01)');
-- overlaps but different key:
INSERT INTO temporal_rng VALUES
(2, '[2000-01-01,2001-01-01)');
-- should fail:
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".
|
