| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/Postgres/src/test/regress/expected/ (Postgres Database Version 18.4©) Datei vom 11.4.2026 mit Größe 122 kB |
|
SSL without_overlaps.out Sprache: unbekannt |
|
|
Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen] -- Tests for WITHOUT OVERLAPS.
-- -- We leave behind several tables to test pg_dump etc: -- temporal_rng, temporal_rng2, -- temporal_fk_rng2rng. SET datestyle TO ISO, YMD; -- -- test input parser -- -- PK with no columns just WITHOUT OVERLAPS: CREATE TABLE temporal_rng ( valid_at daterange, CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS) ); ERROR: constraint using WITHOUT OVERLAPS needs at least two columns -- PK with a range column/PERIOD that isn't there: CREATE TABLE temporal_rng ( id INTEGER, CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); ERROR: column "valid_at" named in key does not exist LINE 3: CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHO... ^ -- PK with a non-range column: CREATE TABLE temporal_rng ( id int4range, valid_at TEXT, CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type LINE 4: CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHO... ^ -- PK with one column plus a range: CREATE TABLE temporal_rng ( -- Since we can't depend on having btree_gist here, -- use an int4range instead of an int. -- (The rangetypes regression test uses the same trick.) id int4range, 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 | int4range | | 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_rn pg_get_indexdef ------------------------------------------------------------------------------- CREATE UNIQUE INDEX temporal_rng_pk ON temporal_rng USING gist (id, valid_at) (1 row) -- PK from LIKE: CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL); \d temporal_rng2 Table "public.temporal_rng2" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | Indexes: "temporal_rng2_pkey" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) DROP TABLE temporal_rng2; -- no PK from INHERITS: CREATE TABLE temporal_rng2 () INHERITS (temporal_rng); \d temporal_rng2 Table "public.temporal_rng2" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | Inherits: temporal_rng DROP TABLE temporal_rng2; DROP TABLE temporal_rng; -- PK in inheriting table: CREATE TABLE temporal_rng ( id int4range, valid_at daterange ); CREATE TABLE temporal_rng2 ( CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) INHERITS (temporal_rng); \d temporal_rng2 Table "public.temporal_rng2" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | Indexes: "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) Inherits: temporal_rng DROP TABLE temporal_rng CASCADE; NOTICE: drop cascades to table temporal_rng2 -- Add PK to already inheriting table: CREATE TABLE temporal_rng ( id int4range, valid_at daterange ); CREATE TABLE temporal_rng2 () INHERITS (temporal_rng); ALTER TABLE temporal_rng2 ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); \d temporal_rng2 Table "public.temporal_rng2" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | Indexes: "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) Inherits: temporal_rng DROP TABLE temporal_rng2; DROP TABLE temporal_rng; -- PK with two columns plus a range: CREATE TABLE temporal_rng2 ( id1 int4range, id2 int4range, valid_at daterange, CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) ); \d temporal_rng2 Table "public.temporal_rng2" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id1 | int4range | | not null | id2 | int4range | | not null | valid_at | daterange | | not null | Indexes: "temporal_rng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk'; pg_get_constraintdef --------------------------------------------------- PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rn pg_get_indexdef -------------------------------------------------------------------------------- CREATE UNIQUE INDEX temporal_rng2_pk ON temporal_rng2 USING gist (id1, id2, valid_at) (1 row) -- PK with a custom range type: CREATE TYPE textrange2 AS range (subtype=text, collation="C"); CREATE TABLE temporal_rng3 ( id int4range, valid_at textrange2, CONSTRAINT temporal_rng3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_pk; DROP TABLE temporal_rng3; DROP TYPE textrange2; -- PK with one column plus a multirange: CREATE TABLE temporal_mltrng ( id int4range, valid_at datemultirange, CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); \d temporal_mltrng Table "public.temporal_mltrng" Column | Type | Collation | Nullable | Default ----------+----------------+-----------+----------+--------- id | int4range | | not null | valid_at | datemultirange | | not null | Indexes: "temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng_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_ml pg_get_indexdef -------------------------------------------------------------------------------- CREATE UNIQUE INDEX temporal_mltrng_pk ON temporal_mltrng USING gist (id, valid_at) (1 row) -- PK with two columns plus a multirange: CREATE TABLE temporal_mltrng2 ( id1 int4range, id2 int4range, valid_at datemultirange, CONSTRAINT temporal_mltrng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) ); \d temporal_mltrng2 Table "public.temporal_mltrng2" Column | Type | Collation | Nullable | Default ----------+----------------+-----------+----------+--------- id1 | int4range | | not null | id2 | int4range | | not null | valid_at | datemultirange | | not null | Indexes: "temporal_mltrng2_pk" PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_p pg_get_constraintdef --------------------------------------------------- PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_ml pg_get_indexdef -------------------------------------------------------------------------------- CREATE UNIQUE INDEX temporal_mltrng2_pk ON temporal_mltrng2 USING gist (id1, id2, valid_at (1 row) -- UNIQUE with no columns just WITHOUT OVERLAPS: CREATE TABLE temporal_rng3 ( valid_at daterange, CONSTRAINT temporal_rng3_uq UNIQUE (valid_at WITHOUT OVERLAPS) ); ERROR: constraint using WITHOUT OVERLAPS needs at least two columns -- UNIQUE with a range column/PERIOD that isn't there: CREATE TABLE temporal_rng3 ( id INTEGER, CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ); ERROR: column "valid_at" named in key does not exist LINE 3: CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT O... ^ -- UNIQUE with a non-range column: CREATE TABLE temporal_rng3 ( id int4range, valid_at TEXT, CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ); ERROR: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type LINE 4: CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT O... ^ -- UNIQUE with one column plus a range: CREATE TABLE temporal_rng3 ( id int4range, valid_at daterange, CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ); \d temporal_rng3 Table "public.temporal_rng3" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id | int4range | | | valid_at | daterange | | | Indexes: "temporal_rng3_uq" UNIQUE (id, valid_at WITHOUT OVERLAPS) SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; pg_get_constraintdef ---------------------------------------- UNIQUE (id, valid_at WITHOUT OVERLAPS) (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rn pg_get_indexdef -------------------------------------------------------------------------------- CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id, valid_at) (1 row) DROP TABLE temporal_rng3; -- UNIQUE with two columns plus a range: CREATE TABLE temporal_rng3 ( id1 int4range, id2 int4range, valid_at daterange, CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) ); \d temporal_rng3 Table "public.temporal_rng3" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id1 | int4range | | | id2 | int4range | | | valid_at | daterange | | | Indexes: "temporal_rng3_uq" UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq'; pg_get_constraintdef ---------------------------------------------- UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS) (1 row) SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rn pg_get_indexdef -------------------------------------------------------------------------------- CREATE UNIQUE INDEX temporal_rng3_uq ON temporal_rng3 USING gist (id1, id2, valid_at) (1 row) DROP TABLE temporal_rng3; -- UNIQUE with a custom range type: CREATE TYPE textrange2 AS range (subtype=text, collation="C"); CREATE TABLE temporal_rng3 ( id int4range, valid_at textrange2, CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ); ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq; DROP TABLE temporal_rng3; DROP TYPE textrange2; -- -- test PRIMARY KEY and UNIQUE constraints' interaction with domains -- -- range over domain: CREATE DOMAIN int4_d as integer check (value <> 10); CREATE TYPE int4_d_range as range (subtype = int4_d); CREATE TABLE temporal_rng4 ( id int4range, valid_at int4_d_range, CONSTRAINT temporal_rng4_pk PRIMARY KEY(id, valid_at WITHOUT OVERLAPS) ); INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)'); -- start bound violates domain ERROR: value for domain int4_d violates check constraint "int4_d_check" LINE 1: INSERT INTO temporal_rng4 VALUES ('[1,11)', '[9,10)'); ^ INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); -- end bound violates domain ERROR: value for domain int4_d violates check constraint "int4_d_check" LINE 1: INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); ^ INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[2,5)'); -- overlaps ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk" DETAIL: Key (id, valid_at)=([1,2), [2,5)) conflicts with existing key (id, valid_at)=([1,2 INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[20,23)'); -- okay INSERT INTO temporal_rng4 VALUES ('[1,2)', '[30,)'); -- null bound is okay DROP TABLE temporal_rng4; -- domain over range: CREATE DOMAIN int4range_d AS int4range CHECK (VALUE <> '[10,11)'); CREATE TABLE temporal_rng4 ( id int4range, valid_at int4range_d, CONSTRAINT temporal_rng4_pk UNIQUE (id, valid_at WITHOUT OVERLAPS) ); INSERT INTO temporal_rng4 VALUES ('[1,2)', '[10,11)'); -- violates domain ERROR: value for domain int4range_d violates check constraint "int4range_d_check" INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[2,13)'); -- overlaps ERROR: conflicting key value violates exclusion constraint "temporal_rng4_pk" DETAIL: Key (id, valid_at)=([1,2), [2,13)) conflicts with existing key (id, valid_at)=([1, INSERT INTO temporal_rng4 VALUES ('[1,2)', '[1,13)'), ('[1,2)', '[20,23)'); -- okay DROP TABLE temporal_rng4; -- -- test ALTER TABLE ADD CONSTRAINT -- CREATE TABLE temporal_rng ( id int4range, valid_at daterange ); ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -- PK with USING INDEX (not possible): CREATE TABLE temporal3 ( id int4range, valid_at daterange ); CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); ALTER TABLE temporal3 ADD CONSTRAINT temporal3_pk PRIMARY KEY USING INDEX idx_temporal3_uq; ERROR: "idx_temporal3_uq" is not a unique index LINE 2: ADD CONSTRAINT temporal3_pk ^ DETAIL: Cannot create a primary key or unique constraint using such an index. DROP TABLE temporal3; -- UNIQUE with USING INDEX (not possible): CREATE TABLE temporal3 ( id int4range, valid_at daterange ); CREATE INDEX idx_temporal3_uq ON temporal3 USING gist (id, valid_at); ALTER TABLE temporal3 ADD CONSTRAINT temporal3_uq UNIQUE USING INDEX idx_temporal3_uq; ERROR: "idx_temporal3_uq" is not a unique index LINE 2: ADD CONSTRAINT temporal3_uq ^ DETAIL: Cannot create a primary key or unique constraint using such an index. DROP TABLE temporal3; -- UNIQUE with USING [UNIQUE] INDEX (possible but not a temporal constraint): CREATE TABLE temporal3 ( id int4range, valid_at daterange ); CREATE UNIQUE INDEX idx_temporal3_uq ON temporal3 (id, valid_at); ALTER TABLE temporal3 ADD CONSTRAINT temporal3_uq UNIQUE USING INDEX idx_temporal3_uq; NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index "idx_temporal3_uq" to "tem DROP TABLE temporal3; -- Add range column and the PK at the same time CREATE TABLE temporal3 ( id int4range ); ALTER TABLE temporal3 ADD COLUMN valid_at daterange, ADD CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); DROP TABLE temporal3; -- Add range column and UNIQUE constraint at the same time CREATE TABLE temporal3 ( id int4range ); ALTER TABLE temporal3 ADD COLUMN valid_at daterange, ADD CONSTRAINT temporal3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); DROP TABLE temporal3; -- -- range PK: test with existing rows -- ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; -- okay: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-0 INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-0 INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-0 INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)); ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT O ALTER TABLE temporal_rng DROP CONSTRAINT temporal_rng_pk; -- should fail: BEGIN; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-0 ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT O ERROR: could not create exclusion constraint "temporal_rng_pk" DETAIL: Key (id, valid_at)=([1,2), [2018-01-02,2018-02-03)) conflicts with key (id, valid ROLLBACK; -- rejects empty: BEGIN; INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT O ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng" ROLLBACK; ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT O DELETE FROM temporal_rng; -- -- range PK: test inserts -- -- okay: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-0 INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-0 INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-0 INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)); -- should fail: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-0 ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" DETAIL: Key (id, valid_at)=([1,2), [2018-01-01,2018-01-05)) conflicts with existing key ( INSERT INTO temporal_rng (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-0 ERROR: null value in column "id" of relation "temporal_rng" violates not-null constraint DETAIL: Failing row contains (null, [2018-01-01,2018-01-05)). INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL); ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constra DETAIL: Failing row contains ([3,4), null). -- rejects empty: INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng" SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2018-01-02,2018-02-03) [1,2) | [2018-03-03,2018-04-04) [2,3) | [2018-01-01,2018-01-05) [3,4) | [2018-01-01,) (4 rows) -- -- range PK: test updates -- -- update the scalar part UPDATE temporal_rng SET id = '[11,12)' WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; -- update the range part UPDATE temporal_rng SET valid_at = '[2020-01-01,2021-01-01)' WHERE id = '[11,12)' AND valid_at @> '2018-01-15'::date; -- update both at once UPDATE temporal_rng SET id = '[21,22)', valid_at = '[2018-01-02,2018-02-03)' WHERE id = '[11,12)' AND valid_at @> '2020-01-15'::date; SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at ---------+------------------------- [1,2) | [2018-03-03,2018-04-04) [2,3) | [2018-01-01,2018-01-05) [3,4) | [2018-01-01,) [21,22) | [2018-01-02,2018-02-03) (4 rows) -- should fail: UPDATE temporal_rng SET id = '[1,2)', valid_at = daterange('2018-03-05', '2018-05-05') WHERE id = '[21,22)'; ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" DETAIL: Key (id, valid_at)=([1,2), [2018-03-05,2018-05-05)) conflicts with existing key ( -- set the scalar part to NULL UPDATE temporal_rng SET id = NULL, valid_at = daterange('2018-03-05', '2018-05-05') WHERE id = '[21,22)'; ERROR: null value in column "id" of relation "temporal_rng" violates not-null constraint DETAIL: Failing row contains (null, [2018-03-05,2018-05-05)). -- set the range part to NULL UPDATE temporal_rng SET id = '[1,2)', valid_at = NULL WHERE id = '[21,22)'; ERROR: null value in column "valid_at" of relation "temporal_rng" violates not-null constra DETAIL: Failing row contains ([1,2), null). -- rejects empty: UPDATE temporal_rng SET id = '[1,2)', valid_at = 'empty' WHERE id = '[21,22)'; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng" SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at ---------+------------------------- [1,2) | [2018-03-03,2018-04-04) [2,3) | [2018-01-01,2018-01-05) [3,4) | [2018-01-01,) [21,22) | [2018-01-02,2018-02-03) (4 rows) -- -- range UQ: test with existing rows -- CREATE TABLE temporal_rng3 ( id int4range, valid_at daterange ); -- okay: INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018- INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018- INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018- INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)) INSERT INTO temporal_rng3 (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01- INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL); ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVE ALTER TABLE temporal_rng3 DROP CONSTRAINT temporal_rng3_uq; -- should fail: BEGIN; INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018- ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVE ERROR: could not create exclusion constraint "temporal_rng3_uq" DETAIL: Key (id, valid_at)=([1,2), [2018-01-02,2018-02-03)) conflicts with key (id, valid ROLLBACK; -- rejects empty: BEGIN; INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVE ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" ROLLBACK; ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVE DELETE FROM temporal_rng3; -- -- range UQ: test inserts -- -- okay: INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018- INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018- INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018- INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', daterange('2018-01-01', NULL)) INSERT INTO temporal_rng3 (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01- INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', NULL); -- should fail: INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018- ERROR: conflicting key value violates exclusion constraint "temporal_rng3_uq" DETAIL: Key (id, valid_at)=([1,2), [2018-01-01,2018-01-05)) conflicts with existing key ( -- rejects empty: INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" SELECT * FROM temporal_rng3 ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2018-01-02,2018-02-03) [1,2) | [2018-03-03,2018-04-04) [2,3) | [2018-01-01,2018-01-05) [3,4) | [2018-01-01,) [3,4) | | [2018-01-01,2018-01-05) (6 rows) -- -- range UQ: test updates -- -- update the scalar part UPDATE temporal_rng3 SET id = '[11,12)' WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; -- update the range part UPDATE temporal_rng3 SET valid_at = '[2020-01-01,2021-01-01)' WHERE id = '[11,12)' AND valid_at @> '2018-01-15'::date; -- update both at once UPDATE temporal_rng3 SET id = '[21,22)', valid_at = '[2018-01-02,2018-02-03)' WHERE id = '[11,12)' AND valid_at @> '2020-01-15'::date; -- set the scalar part to NULL UPDATE temporal_rng3 SET id = NULL, valid_at = daterange('2020-01-01', '2021-01-01') WHERE id = '[21,22)'; -- set the range part to NULL UPDATE temporal_rng3 SET id = '[1,2)', valid_at = NULL WHERE id IS NULL AND valid_at @> '2020-06-01'::date; SELECT * FROM temporal_rng3 ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2018-03-03,2018-04-04) [1,2) | [2,3) | [2018-01-01,2018-01-05) [3,4) | [2018-01-01,) [3,4) | | [2018-01-01,2018-01-05) (6 rows) -- should fail: UPDATE temporal_rng3 SET valid_at = daterange('2018-03-01', '2018-05-05') WHERE id = '[1,2)' AND valid_at IS NULL; ERROR: conflicting key value violates exclusion constraint "temporal_rng3_uq" DETAIL: Key (id, valid_at)=([1,2), [2018-03-01,2018-05-05)) conflicts with existing key ( -- rejects empty: UPDATE temporal_rng3 SET valid_at = 'empty' WHERE id = '[1,2)' AND valid_at IS NULL; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" -- still rejects empty when scalar part is NULL: UPDATE temporal_rng3 SET id = NULL, valid_at = 'empty' WHERE id = '[1,2)' AND valid_at IS NULL; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" SELECT * FROM temporal_rng3 ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2018-03-03,2018-04-04) [1,2) | [2,3) | [2018-01-01,2018-01-05) [3,4) | [2018-01-01,) [3,4) | | [2018-01-01,2018-01-05) (6 rows) DROP TABLE temporal_rng3; -- -- multirange PK: test with existing rows -- ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk; -- okay: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2 INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2 ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WI ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk; -- should fail: BEGIN; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WI ERROR: could not create exclusion constraint "temporal_mltrng_pk" DETAIL: Key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}) conflicts with key (id, val ROLLBACK; -- rejects empty: BEGIN; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WI ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng ROLLBACK; ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WI DELETE FROM temporal_mltrng; -- -- multirange PK: test inserts -- -- okay: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2 INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2 -- should fail: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 ERROR: conflicting key value violates exclusion constraint "temporal_mltrng_pk" DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}) conflicts with existing ke INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018 ERROR: null value in column "id" of relation "temporal_mltrng" violates not-null constraint DETAIL: Failing row contains (null, {[2018-01-01,2018-01-05)}). INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL); ERROR: null value in column "valid_at" of relation "temporal_mltrng" violates not-null cons DETAIL: Failing row contains ([3,4), null). -- rejects empty: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2018-01-02,2018-02-03)} [1,2) | {[2018-03-03,2018-04-04)} [2,3) | {[2018-01-01,2018-01-05)} [3,4) | {[2018-01-01,)} (4 rows) -- -- multirange PK: test updates -- -- update the scalar part UPDATE temporal_mltrng SET id = '[11,12)' WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; -- update the multirange part UPDATE temporal_mltrng SET valid_at = '{[2020-01-01,2021-01-01)}' WHERE id = '[11,12)' AND valid_at @> '2018-01-15'::date; -- update both at once UPDATE temporal_mltrng SET id = '[21,22)', valid_at = '{[2018-01-02,2018-02-03)}' WHERE id = '[11,12)' AND valid_at @> '2020-01-15'::date; SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at ---------+--------------------------- [1,2) | {[2018-03-03,2018-04-04)} [2,3) | {[2018-01-01,2018-01-05)} [3,4) | {[2018-01-01,)} [21,22) | {[2018-01-02,2018-02-03)} (4 rows) -- should fail: UPDATE temporal_mltrng SET id = '[1,2)', valid_at = datemultirange(daterange('2018-03-05', '2018-05-05')) WHERE id = '[21,22)'; ERROR: conflicting key value violates exclusion constraint "temporal_mltrng_pk" DETAIL: Key (id, valid_at)=([1,2), {[2018-03-05,2018-05-05)}) conflicts with existing ke -- set the scalar part to NULL UPDATE temporal_mltrng SET id = NULL, valid_at = datemultirange(daterange('2018-03-05', '2018-05-05')) WHERE id = '[21,22)'; ERROR: null value in column "id" of relation "temporal_mltrng" violates not-null constraint DETAIL: Failing row contains (null, {[2018-03-05,2018-05-05)}). -- set the multirange part to NULL UPDATE temporal_mltrng SET id = '[1,2)', valid_at = NULL WHERE id = '[21,22)'; ERROR: null value in column "valid_at" of relation "temporal_mltrng" violates not-null cons DETAIL: Failing row contains ([1,2), null). -- rejects empty: UPDATE temporal_mltrng SET id = '[1,2)', valid_at = '{}' WHERE id = '[21,22)'; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at ---------+--------------------------- [1,2) | {[2018-03-03,2018-04-04)} [2,3) | {[2018-01-01,2018-01-05)} [3,4) | {[2018-01-01,)} [21,22) | {[2018-01-02,2018-02-03)} (4 rows) -- -- multirange UQ: test with existing rows -- CREATE TABLE temporal_mltrng3 ( id int4range, valid_at datemultirange ); -- okay: INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange(' INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange(' INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('201 INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL); ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITH ALTER TABLE temporal_mltrng3 DROP CONSTRAINT temporal_mltrng3_uq; -- should fail: BEGIN; INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITH ERROR: could not create exclusion constraint "temporal_mltrng3_uq" DETAIL: Key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}) conflicts with key (id, val ROLLBACK; -- rejects empty: BEGIN; INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITH ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng ROLLBACK; ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITH DELETE FROM temporal_mltrng3; -- -- multirange UQ: test inserts -- -- okay: INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange(' INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange(' INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('201 INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL); -- should fail: INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' ERROR: conflicting key value violates exclusion constraint "temporal_mltrng3_uq" DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-01-05)}) conflicts with existing ke -- rejects empty: INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2018-01-02,2018-02-03)} [1,2) | {[2018-03-03,2018-04-04)} [2,3) | {[2018-01-01,2018-01-05)} [3,4) | {[2018-01-01,)} [3,4) | | {[2018-01-01,2018-01-05)} (6 rows) -- -- multirange UQ: test updates -- -- update the scalar part UPDATE temporal_mltrng3 SET id = '[11,12)' WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; -- update the multirange part UPDATE temporal_mltrng3 SET valid_at = '{[2020-01-01,2021-01-01)}' WHERE id = '[11,12)' AND valid_at @> '2018-01-15'::date; -- update both at once UPDATE temporal_mltrng3 SET id = '[21,22)', valid_at = '{[2018-01-02,2018-02-03)}' WHERE id = '[11,12)' AND valid_at @> '2020-01-15'::date; -- set the scalar part to NULL UPDATE temporal_mltrng3 SET id = NULL, valid_at = datemultirange(daterange('2020-01-01', '2021-01-01')) WHERE id = '[21,22)'; -- set the multirange part to NULL UPDATE temporal_mltrng3 SET id = '[1,2)', valid_at = NULL WHERE id IS NULL AND valid_at @> '2020-06-01'::date; SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2018-03-03,2018-04-04)} [1,2) | [2,3) | {[2018-01-01,2018-01-05)} [3,4) | {[2018-01-01,)} [3,4) | | {[2018-01-01,2018-01-05)} (6 rows) -- should fail: UPDATE temporal_mltrng3 SET valid_at = datemultirange(daterange('2018-03-01', '2018-05-05')) WHERE id = '[1,2)' AND valid_at IS NULL; ERROR: conflicting key value violates exclusion constraint "temporal_mltrng3_uq" DETAIL: Key (id, valid_at)=([1,2), {[2018-03-01,2018-05-05)}) conflicts with existing ke -- rejects empty: UPDATE temporal_mltrng3 SET valid_at = '{}' WHERE id = '[1,2)' AND valid_at IS NULL; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng -- still rejects empty when scalar part is NULL: UPDATE temporal_mltrng3 SET id = NULL, valid_at = '{}' WHERE id = '[1,2)' AND valid_at IS NULL; ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2018-03-03,2018-04-04)} [1,2) | [2,3) | {[2018-01-01,2018-01-05)} [3,4) | {[2018-01-01,)} [3,4) | | {[2018-01-01,2018-01-05)} (6 rows) DROP TABLE temporal_mltrng3; -- -- test a range with both a PK and a UNIQUE constraint -- CREATE TABLE temporal3 ( id int4range, valid_at daterange, id2 int8range, name TEXT, CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal3_uniq UNIQUE (id2, valid_at WITHOUT OVERLAPS) ); INSERT INTO temporal3 (id, valid_at, id2, name) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'), '[7,8)', 'foo'), ('[2,3)', daterange('2000-01-01', '2010-01-01'), '[9,10)', 'bar') ; DROP TABLE temporal3; -- -- test changing the PK's dependencies -- CREATE TABLE temporal3 ( id int4range, valid_at daterange, CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL; ERROR: column "valid_at" is in a primary key ALTER TABLE temporal3 ALTER COLUMN valid_at TYPE tstzrange USING tstzrange(lower(valid_at ALTER TABLE temporal3 RENAME COLUMN valid_at TO valid_thru; ALTER TABLE temporal3 DROP COLUMN valid_thru; DROP TABLE temporal3; -- -- test PARTITION BY for ranges -- -- temporal PRIMARY KEY: CREATE TABLE temporal_partitioned ( id int4range, valid_at daterange, name text, CONSTRAINT temporal_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); SELECT * FROM temporal_partitioned ORDER BY id, valid_at; id | valid_at | name -------+-------------------------+------- [1,2) | [2000-01-01,2000-02-01) | one [1,2) | [2000-02-01,2000-03-01) | one [3,4) | [2000-01-01,2010-01-01) | three (3 rows) SELECT * FROM tp1 ORDER BY id, valid_at; id | valid_at | name -------+-------------------------+------ [1,2) | [2000-01-01,2000-02-01) | one [1,2) | [2000-02-01,2000-03-01) | one (2 rows) SELECT * FROM tp2 ORDER BY id, valid_at; id | valid_at | name -------+-------------------------+------- [3,4) | [2000-01-01,2010-01-01) | three (1 row) DROP TABLE temporal_partitioned; -- temporal UNIQUE: CREATE TABLE temporal_partitioned ( id int4range, valid_at daterange, name text, CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,2)', '[2,3)'); CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5)'); INSERT INTO temporal_partitioned (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[3,4)', daterange('2000-01-01', '2010-01-01'), 'three'); SELECT * FROM temporal_partitioned ORDER BY id, valid_at; id | valid_at | name -------+-------------------------+------- [1,2) | [2000-01-01,2000-02-01) | one [1,2) | [2000-02-01,2000-03-01) | one [3,4) | [2000-01-01,2010-01-01) | three (3 rows) SELECT * FROM tp1 ORDER BY id, valid_at; id | valid_at | name -------+-------------------------+------ [1,2) | [2000-01-01,2000-02-01) | one [1,2) | [2000-02-01,2000-03-01) | one (2 rows) SELECT * FROM tp2 ORDER BY id, valid_at; id | valid_at | name -------+-------------------------+------- [3,4) | [2000-01-01,2010-01-01) | three (1 row) DROP TABLE temporal_partitioned; -- ALTER TABLE REPLICA IDENTITY \d temporal_rng Table "public.temporal_rng" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | Indexes: "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk; \d temporal_rng Table "public.temporal_rng" Column | Type | Collation | Nullable | Default ----------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | Indexes: "temporal_rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) REPLICA IDENTITY -- -- ON CONFLICT: ranges -- TRUNCATE temporal_rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-0 -- with a conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-0 -- id matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-0 -- date matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-0 SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) [1,2) | [2010-01-01,2020-01-01) [2,3) | [2005-01-01,2006-01-01) (3 rows) TRUNCATE temporal_rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-0 -- with a conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- id matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- date matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) (1 row) TRUNCATE temporal_rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-0 -- with a conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-0 -- id matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-0 -- date matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-0 SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) [1,2) | [2010-01-01,2020-01-01) [2,3) | [2005-01-01,2006-01-01) (3 rows) TRUNCATE temporal_rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-0 -- with a conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- id matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- date matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) (1 row) TRUNCATE temporal_rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-0 -- with a conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-0 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- id matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-0 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- date matches but no conflict INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-0 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints SELECT * FROM temporal_rng ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) (1 row) -- with a UNIQUE constraint: CREATE TABLE temporal3 ( id int4range, valid_at daterange, CONSTRAINT temporal3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ); TRUNCATE temporal3; INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-0 -- with a conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-0 -- id matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-0 -- date matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-0 SELECT * FROM temporal3 ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) [1,2) | [2010-01-01,2020-01-01) [2,3) | [2005-01-01,2006-01-01) (3 rows) TRUNCATE temporal3; INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-0 -- with a conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- id matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- date matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SELECT * FROM temporal3 ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) (1 row) TRUNCATE temporal3; INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-0 -- with a conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-0 -- id matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-0 -- date matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-0 SELECT * FROM temporal3 ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) [1,2) | [2010-01-01,2020-01-01) [2,3) | [2005-01-01,2006-01-01) (3 rows) TRUNCATE temporal3; INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-0 -- with a conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- id matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- date matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-0 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SELECT * FROM temporal3 ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) (1 row) TRUNCATE temporal3; INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-0 -- with a conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-0 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- id matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-0 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- date matches but no conflict INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-0 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints SELECT * FROM temporal3 ORDER BY id, valid_at; id | valid_at -------+------------------------- [1,2) | [2000-01-01,2010-01-01) (1 row) DROP TABLE temporal3; -- -- ON CONFLICT: multiranges -- TRUNCATE temporal_mltrng; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- with a conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- id matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- date matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2 SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} [1,2) | {[2010-01-01,2020-01-01)} [2,3) | {[2005-01-01,2006-01-01)} (3 rows) TRUNCATE temporal_mltrng; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- with a conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- id matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- date matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} (1 row) TRUNCATE temporal_mltrng; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- with a conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- id matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- date matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2 SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} [1,2) | {[2010-01-01,2020-01-01)} [2,3) | {[2005-01-01,2006-01-01)} (3 rows) TRUNCATE temporal_mltrng; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- with a conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- id matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- date matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2 ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} (1 row) TRUNCATE temporal_mltrng; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 -- with a conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- id matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- date matches but no conflict INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2 ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints SELECT * FROM temporal_mltrng ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} (1 row) -- with a UNIQUE constraint: CREATE TABLE temporal_mltrng3 ( id int4range, valid_at datemultirange, CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) ); TRUNCATE temporal_mltrng3; INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- with a conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- id matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- date matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange(' SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} [1,2) | {[2010-01-01,2020-01-01)} [2,3) | {[2005-01-01,2006-01-01)} (3 rows) TRUNCATE temporal_mltrng3; INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- with a conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- id matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- date matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange(' ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} (1 row) TRUNCATE temporal_mltrng3; INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- with a conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- id matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- date matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange(' SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} [1,2) | {[2010-01-01,2020-01-01)} [2,3) | {[2005-01-01,2006-01-01)} (3 rows) TRUNCATE temporal_mltrng3; INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- with a conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- id matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification -- date matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange(' ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} (1 row) TRUNCATE temporal_mltrng3; INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' -- with a conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- id matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange(' ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints -- date matches but no conflict INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange(' ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; id | valid_at -------+--------------------------- [1,2) | {[2000-01-01,2010-01-01)} (1 row) DROP TABLE temporal_mltrng3; -- -- test FK dependencies -- -- can't drop a range referenced by an FK, unless with CASCADE CREATE TABLE temporal3 ( id int4range, valid_at daterange, CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, 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 temporal3 (id, PERIOD valid_at) ); ALTER TABLE temporal3 DROP COLUMN valid_at; ERROR: cannot drop column valid_at of table temporal3 because other objects depend on it DETAIL: constraint temporal_fk_rng2rng_fk on table temporal_fk_rng2rng depends on column HINT: Use DROP ... CASCADE to drop the dependent objects too. ALTER TABLE temporal3 DROP COLUMN valid_at CASCADE; NOTICE: drop cascades to constraint temporal_fk_rng2rng_fk on table temporal_fk_rng2rng DROP TABLE temporal_fk_rng2rng; DROP TABLE temporal3; -- -- test FOREIGN KEY, range references range -- -- test table setup DROP TABLE temporal_rng; CREATE TABLE temporal_rng (id int4range, valid_at daterange); ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -- Can't create a FK with a mismatched range type CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at int4range, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id, PERIOD valid_at) ); ERROR: foreign key constraint "temporal_fk_rng2rng_fk2" cannot be implemented DETAIL: Key columns "valid_at" of the referencing table and "valid_at" of the referenced tabl -- works: PERIOD for both referenced and referencing CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, 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) ); DROP TABLE temporal_fk_rng2rng; -- with mismatched PERIOD columns: -- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) -- REFERENCES part should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, 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, valid_at) ); ERROR: foreign key uses PERIOD on the referencing table but not the referenced table -- (parent_id, valid_at) REFERENCES (id, valid_at) -- both should specify PERIOD: CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_rng (id, valid_at) ); ERROR: foreign key must use PERIOD when referencing a primary key using WITHOUT OVERLAPS -- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) -- FOREIGN KEY part should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_rng (id, PERIOD valid_at) ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- (parent_id, valid_at) REFERENCES [implicit] -- FOREIGN KEY part should specify PERIOD CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_rng ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- (parent_id, PERIOD valid_at) REFERENCES (id) CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, 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) ); ERROR: foreign key uses PERIOD on the referencing table but not the referenced table -- (parent_id) REFERENCES (id, PERIOD valid_at) CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) REFERENCES temporal_rng (id, PERIOD valid_at) ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- with inferred PK on the referenced table: -- (parent_id, PERIOD valid_at) REFERENCES [implicit] CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, 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 ); DROP TABLE temporal_fk_rng2rng; -- (parent_id) REFERENCES [implicit] CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id) REFERENCES temporal_rng ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- should fail because of duplicate referenced columns: CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD parent_id) REFERENCES temporal_rng (id, PERIOD id) ); ERROR: foreign key referenced-columns list must not contain duplicates -- Two scalar columns DROP TABLE temporal_rng2; CREATE TABLE temporal_rng2 ( id1 int4range, id2 int4range, valid_at daterange, CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) ); CREATE TABLE temporal_fk2_rng2rng ( id int4range, valid_at daterange, parent_id1 int4range, parent_id2 int4range, CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at) ); \d temporal_fk2_rng2rng Table "public.temporal_fk2_rng2rng" Column | Type | Collation | Nullable | Default ------------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | parent_id1 | int4range | | | parent_id2 | int4range | | | Indexes: "temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) Foreign-key constraints: "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFEREN DROP TABLE temporal_fk2_rng2rng; -- -- test ALTER TABLE ADD CONSTRAINT -- CREATE TABLE temporal_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng (id, PERIOD valid_at); -- Two scalar columns: CREATE TABLE temporal_fk2_rng2rng ( id int4range, valid_at daterange, parent_id1 int4range, parent_id2 int4range, CONSTRAINT temporal_fk2_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); ALTER TABLE temporal_fk2_rng2rng ADD CONSTRAINT temporal_fk2_rng2rng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_rng2 (id1, id2, PERIOD valid_at); \d temporal_fk2_rng2rng Table "public.temporal_fk2_rng2rng" Column | Type | Collation | Nullable | Default ------------+-----------+-----------+----------+--------- id | int4range | | not null | valid_at | daterange | | not null | parent_id1 | int4range | | | parent_id2 | int4range | | | Indexes: "temporal_fk2_rng2rng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) Foreign-key constraints: "temporal_fk2_rng2rng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFEREN -- with inferred PK on the referenced table, and wrong column type: ALTER TABLE temporal_fk_rng2rng DROP CONSTRAINT temporal_fk_rng2rng_fk, ALTER COLUMN valid_at TYPE tsrange USING tsrange(lower(valid_at), upper(valid_at)); ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; ERROR: foreign key constraint "temporal_fk_rng2rng_fk" cannot be implemented DETAIL: Key columns "valid_at" of the referencing table and "valid_at" of the referenced tabl ALTER TABLE temporal_fk_rng2rng ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid -- with inferred PK on the referenced table: ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; -- should fail because of duplicate referenced columns: ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk2 FOREIGN KEY (parent_id, PERIOD parent_id) REFERENCES temporal_rng (id, PERIOD id); ERROR: foreign key referenced-columns list must not contain duplicates -- -- test with rows already -- DELETE FROM temporal_fk_rng2rng; DELETE FROM temporal_rng; INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03')), ('[1,2)', daterange('2018-03-03', '2018-04-04')), ('[2,3)', daterange('2018-01-01', '2018-01-05')), ('[3,4)', daterange('2018-01-01', NULL)); ALTER TABLE temporal_fk_rng2rng DROP CONSTRAINT temporal_fk_rng2rng_fk; INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('20 ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; ALTER TABLE temporal_fk_rng2rng DROP CONSTRAINT temporal_fk_rng2rng_fk; INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('20 -- should fail: ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "tem DETAIL: Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-04-01)) is not present in tabl -- okay again: DELETE FROM temporal_fk_rng2rng; ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; -- -- test pg_get_constraintdef -- SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_rng2rn pg_get_constraintdef -------------------------------------------------------------------------------- FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng(id, PERIOD valid_at) (1 row) -- -- test FK referencing inserts -- INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('20 -- should fail: INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('20 ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "tem DETAIL: Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-04-01)) is not present in tabl -- now it should work: INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-02-03', '2018-0 INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('20 -- -- test FK referencing updates -- -- slide the edge across a referenced transition: UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-02-20') WHERE id -- should fail: UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-05-01') WHERE id ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "tem DETAIL: Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-05-01)) is not present in tabl UPDATE temporal_fk_rng2rng SET parent_id = '[8,9)' WHERE id = '[1,2)'; ERROR: insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "tem DETAIL: Key (parent_id, valid_at)=([8,9), [2018-01-02,2018-02-20)) is not present in tabl -- ALTER FK DEFERRABLE BEGIN; INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')), ('[5,6)', daterange('2018-02-01', '2018-03-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); ALTER TABLE temporal_fk_rng2rng ALTER CONSTRAINT temporal_fk_rng2rng_fk DEFERRABLE INITIALLY DEFERRED; DELETE FROM temporal_rng WHERE id = '[5,6)'; --should not fail yet. COMMIT; -- should fail here. ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_f DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from tabl -- -- test FK referenced updates NO ACTION -- TRUNCATE temporal_rng, temporal_fk_rng2rng; ALTER TABLE temporal_fk_rng2rng DROP CONSTRAINT temporal_fk_rng2rng_fk; ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON UPDATE NO ACTION; -- a PK update that succeeds because the numeric id isn't referenced: INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-0 UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6) -- a PK update that succeeds even though the numeric id is referenced because the range isn't: DELETE FROM temporal_rng WHERE id = '[5,6)'; INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')), ('[5,6)', daterange('2018-02-01', '2018-03-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); UPDATE temporal_rng SET valid_at = daterange('2016-02-01', '2016-03-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); -- A PK update sliding the edge between two referenced rows: INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2018-02-01')), ('[6,7)', daterange('2018-02-01', '2018-03-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('2018-01-15', '2018-02-15'), '[6,7)'); UPDATE temporal_rng SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN daterange('2018-01-01', '2018 WHEN lower(valid_at) = '2018-02-01' THEN daterange('2018-01-05', '2018-03-01') END WHERE id = '[6,7)'; -- a PK update shrinking the referenced range but still valid: -- There are two references: one fulfilled by the first pk row, -- the other fulfilled by both pk rows combined. INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-01', '2018-03-01')), ('[1,2)', daterange('2018-03-01', '2018-06-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2018-01-15', '2018-02-01'), '[1,2)'), ('[2,3)', daterange('2018-01-15', '2018-05-01'), '[1,2)'); UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-03-01') WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; -- a PK update growing the referenced range is fine: UPDATE temporal_rng SET valid_at = daterange('2018-01-01', '2018-03-01') WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date; -- a PK update shrinking the referenced range and changing the id invalidates the whole range (e UPDATE temporal_rng SET id = '[2,3)', valid_at = daterange('2018-01-15', '2018-03-01') WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_f DETAIL: Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from tabl -- a PK update changing only the id invalidates the whole range (error): UPDATE temporal_rng SET id = '[2,3)' WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_f DETAIL: Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from tabl -- a PK update that loses time from both ends, but is still valid: INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-03-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[5,6)', daterange('2018-01-15', '2018-02-01'), '[2,3)'); UPDATE temporal_rng SET valid_at = daterange('2018-01-15', '2018-02-15') WHERE id = '[2,3)'; -- a PK update that fails because both are referenced: UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_f DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from tabl -- a PK update that fails because both are referenced, but not 'til commit: BEGIN; ALTER TABLE temporal_fk_rng2rng ALTER CONSTRAINT temporal_fk_rng2rng_fk DEFERRABLE INITIALLY DEFERRED; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); COMMIT; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_f DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from tabl -- changing the scalar part fails: UPDATE temporal_rng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_f DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from tabl -- then delete the objecting FK record and the same PK update succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; UPDATE temporal_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); -- -- test FK referenced updates RESTRICT -- TRUNCATE temporal_rng, temporal_fk_rng2rng; ALTER TABLE temporal_fk_rng2rng DROP CONSTRAINT temporal_fk_rng2rng_fk; ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON UPDATE RESTRICT; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- test FK referenced deletes NO ACTION -- TRUNCATE temporal_rng, temporal_fk_rng2rng; ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng; -- a PK delete that succeeds because the numeric id isn't referenced: INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-0 DELETE FROM temporal_rng WHERE id = '[5,6)'; -- a PK delete that succeeds even though the numeric id is referenced because the range isn't: INSERT INTO temporal_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01-01', '2018-02-01')), ('[5,6)', daterange('2018-02-01', '2018-03-01')); INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)'); DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03 -- a PK delete that fails because both are referenced: DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02 ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_f DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from tabl -- a PK delete that fails because both are referenced, but not 'til commit: BEGIN; ALTER TABLE temporal_fk_rng2rng ALTER CONSTRAINT temporal_fk_rng2rng_fk DEFERRABLE INITIALLY DEFERRED; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02 COMMIT; ERROR: update or delete on table "temporal_rng" violates foreign key constraint "temporal_f DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from tabl -- then delete the objecting FK record and the same PK delete succeeds: DELETE FROM temporal_fk_rng2rng WHERE id = '[3,4)'; DELETE FROM temporal_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02 -- -- test FK referenced deletes RESTRICT -- TRUNCATE temporal_rng, temporal_fk_rng2rng; ALTER TABLE temporal_fk_rng2rng DROP CONSTRAINT temporal_fk_rng2rng_fk; ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE RESTRICT; ERROR: unsupported ON DELETE action for foreign key constraint using PERIOD -- -- test ON UPDATE/DELETE options -- -- test FK referenced updates CASCADE INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-0 INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[4,5)', daterange('20 ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE CASCADE ON UPDATE CASCADE; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- test FK referenced updates SET NULL INSERT INTO temporal_rng (id, valid_at) VALUES ('[9,10)', daterange('2018-01-01', '2021- INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[6,7)', daterange('20 ALTER TABLE temporal_fk_rng2rng ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET NULL ON UPDATE SET NULL; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- test FK referenced updates SET DEFAULT INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null)); INSERT INTO temporal_rng (id, valid_at) VALUES ('[12,13)', daterange('2018-01-01', '2021 INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[8,9)', daterange('20 ALTER TABLE temporal_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', ADD CONSTRAINT temporal_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- test FOREIGN KEY, multirange references multirange -- -- test table setup DROP TABLE temporal_mltrng; CREATE TABLE temporal_mltrng ( id int4range, valid_at datemultirange); ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); -- Can't create a FK with a mismatched multirange type CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at int4multirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk2 PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk2 FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at) ); ERROR: foreign key constraint "temporal_fk_mltrng2mltrng_fk2" cannot be implemented DETAIL: Key columns "valid_at" of the referencing table and "valid_at" of the referenced tabl CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at) ); DROP TABLE temporal_fk_mltrng2mltrng; -- with mismatched PERIOD columns: -- (parent_id, PERIOD valid_at) REFERENCES (id, valid_at) -- REFERENCES part should specify PERIOD CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, valid_at) ); ERROR: foreign key uses PERIOD on the referencing table but not the referenced table -- (parent_id, valid_at) REFERENCES (id, valid_at) -- both should specify PERIOD: CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_mltrng (id, valid_at) ); ERROR: foreign key must use PERIOD when referencing a primary key using WITHOUT OVERLAPS -- (parent_id, valid_at) REFERENCES (id, PERIOD valid_at) -- FOREIGN KEY part should specify PERIOD CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at) ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- (parent_id, valid_at) REFERENCES [implicit] -- FOREIGN KEY part should specify PERIOD CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, valid_at) REFERENCES temporal_mltrng ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- (parent_id, PERIOD valid_at) REFERENCES (id) CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id) ); ERROR: foreign key uses PERIOD on the referencing table but not the referenced table -- (parent_id) REFERENCES (id, PERIOD valid_at) CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) REFERENCES temporal_mltrng (id, PERIOD valid_at) ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- with inferred PK on the referenced table: -- (parent_id, PERIOD valid_at) REFERENCES [implicit] CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng ); DROP TABLE temporal_fk_mltrng2mltrng; -- (parent_id) REFERENCES [implicit] CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id) REFERENCES temporal_mltrng ); ERROR: foreign key uses PERIOD on the referenced table but not the referencing table -- should fail because of duplicate referenced columns: CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD parent_id) REFERENCES temporal_mltrng (id, PERIOD id) ); ERROR: foreign key referenced-columns list must not contain duplicates -- Two scalar columns DROP TABLE temporal_mltrng2; CREATE TABLE temporal_mltrng2 ( id1 int4range, id2 int4range, valid_at datemultirange, CONSTRAINT temporal_mltrng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS) ); CREATE TABLE temporal_fk2_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id1 int4range, parent_id2 int4range, CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), CONSTRAINT temporal_fk2_mltrng2mltrng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD va REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at) ); \d temporal_fk2_mltrng2mltrng Table "public.temporal_fk2_mltrng2mltrng" Column | Type | Collation | Nullable | Default ------------+----------------+-----------+----------+--------- id | int4range | | not null | valid_at | datemultirange | | not null | parent_id1 | int4range | | | parent_id2 | int4range | | | Indexes: "temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) Foreign-key constraints: "temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) R DROP TABLE temporal_fk2_mltrng2mltrng; -- -- test ALTER TABLE ADD CONSTRAINT -- CREATE TABLE temporal_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at); -- Two scalar columns: CREATE TABLE temporal_fk2_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id1 int4range, parent_id2 int4range, CONSTRAINT temporal_fk2_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ); ALTER TABLE temporal_fk2_mltrng2mltrng ADD CONSTRAINT temporal_fk2_mltrng2mltrng_fk FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) REFERENCES temporal_mltrng2 (id1, id2, PERIOD valid_at); \d temporal_fk2_mltrng2mltrng Table "public.temporal_fk2_mltrng2mltrng" Column | Type | Collation | Nullable | Default ------------+----------------+-----------+----------+--------- id | int4range | | not null | valid_at | datemultirange | | not null | parent_id1 | int4range | | | parent_id2 | int4range | | | Indexes: "temporal_fk2_mltrng2mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) Foreign-key constraints: "temporal_fk2_mltrng2mltrng_fk" FOREIGN KEY (parent_id1, parent_id2, PERIOD valid_at) R -- should fail because of duplicate referenced columns: ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk2 FOREIGN KEY (parent_id, PERIOD parent_id) REFERENCES temporal_mltrng (id, PERIOD id); ERROR: foreign key referenced-columns list must not contain duplicates -- -- test with rows already -- DELETE FROM temporal_fk_mltrng2mltrng; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03'))), ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04'))), ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05'))), ('[3,4)', datemultirange(daterange('2018-01-01', NULL))); ALTER TABLE temporal_fk_mltrng2mltrng DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemul ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at); ALTER TABLE temporal_fk_mltrng2mltrng DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemul -- should fail: ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at); ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constrai DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in ta -- okay again: DELETE FROM temporal_fk_mltrng2mltrng; ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at); -- -- test pg_get_constraintdef -- SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_fk_mltrng pg_get_constraintdef -------------------------------------------------------------------------------- FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng(id, PERIOD valid_at) (1 row) -- -- test FK referencing inserts -- INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemul -- should fail: INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemul ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constrai DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in ta -- now it should work: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2 INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemul -- -- test FK referencing updates -- -- slide the edge across a referenced transition: UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02' -- should fail: UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02' ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constrai DETAIL: Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-05-01)}) is not present in ta UPDATE temporal_fk_mltrng2mltrng SET parent_id = '[8,9)' WHERE id = '[1,2)'; ERROR: insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constrai DETAIL: Key (parent_id, valid_at)=([8,9), {[2018-01-02,2018-02-20)}) is not present in ta -- ALTER FK DEFERRABLE BEGIN; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); ALTER TABLE temporal_fk_mltrng2mltrng ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk DEFERRABLE INITIALLY DEFERRED; DELETE FROM temporal_mltrng WHERE id = '[5,6)'; --should not fail yet. COMMIT; -- should fail here. ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "tempora DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from ta -- -- test FK referenced updates NO ACTION -- TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; ALTER TABLE temporal_fk_mltrng2mltrng DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at) ON UPDATE NO ACTION; -- a PK update that succeeds because the numeric id isn't referenced: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2 UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02- -- a PK update that succeeds even though the numeric id is referenced because the range isn't: DELETE FROM temporal_mltrng WHERE id = '[5,6)'; INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)'); UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03- WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); -- A PK update sliding the edge between two referenced rows: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[6,7)', datemultirange(daterange('2018-01-01', '2018-02-01'))), ('[6,7)', datemultirange(daterange('2018-02-01', '2018-03-01'))); INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[4,5)', datemultirange(daterange('2018-01-15', '2018-02-15')), '[6,7)'); UPDATE temporal_mltrng SET valid_at = CASE WHEN lower(valid_at) = '2018-01-01' THEN datemultirange(daterange('20 WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018- WHERE id = '[6,7)'; -- a PK update shrinking the referenced multirange but still valid: -- There are two references: one fulfilled by the first pk row, -- the other fulfilled by both pk rows combined. INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-03-01'))), ('[1,2)', datemultirange(daterange('2018-03-01', '2018-06-01'))); INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[1,2)'), ('[2,3)', datemultirange(daterange('2018-01-15', '2018-05-01')), '[1,2)'); UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-03- WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; -- a PK update growing the referenced multirange is fine: UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-03- WHERE id = '[1,2)' AND valid_at @> '2018-01-25'::date; -- a PK update shrinking the referenced multirange and changing the id invalidates the whole mu UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(daterange('2018-01-15 WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "tempora DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from ta -- a PK update changing only the id invalidates the whole multirange (error): UPDATE temporal_mltrng SET id = '[2,3)' WHERE id = '[1,2)' AND valid_at @> '2018-01-15'::date; ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "tempora DETAIL: Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from ta -- a PK update that loses time from both ends, but is still valid: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-03-01'))); INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[5,6)', datemultirange(daterange('2018-01-15', '2018-02-01')), '[2,3)'); UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2018-01-15', '2018-02- WHERE id = '[2,3)'; -- a PK update that fails because both are referenced: UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02- WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "tempora DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from ta -- a PK update that fails because both are referenced, but not 'til commit: BEGIN; ALTER TABLE temporal_fk_mltrng2mltrng ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk DEFERRABLE INITIALLY DEFERRED; UPDATE temporal_mltrng SET valid_at = datemultirange(daterange('2016-01-01', '2016-02- WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); COMMIT; ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "tempora DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from ta -- changing the scalar part fails: UPDATE temporal_mltrng SET id = '[7,8)' WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "tempora DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from ta -- -- test FK referenced updates RESTRICT -- TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; ALTER TABLE temporal_fk_mltrng2mltrng DROP CONSTRAINT temporal_fk_mltrng2mltrng_fk; ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at) ON UPDATE RESTRICT; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- test FK referenced deletes NO ACTION -- TRUNCATE temporal_mltrng, temporal_fk_mltrng2mltrng; ALTER TABLE temporal_fk_mltrng2mltrng ADD CONSTRAINT temporal_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_mltrng (id, PERIOD valid_at); -- a PK delete that succeeds because the numeric id isn't referenced: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2 DELETE FROM temporal_mltrng WHERE id = '[5,6)'; -- a PK delete that succeeds even though the numeric id is referenced because the range isn't: INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-01-01', '2018-02-01'))), ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01'))); INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemul DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('20 -- a PK delete that fails because both are referenced: DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('20 ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "tempora DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from ta -- a PK delete that fails because both are referenced, but not 'til commit: BEGIN; ALTER TABLE temporal_fk_mltrng2mltrng ALTER CONSTRAINT temporal_fk_mltrng2mltrng_fk DEFERRABLE INITIALLY DEFERRED; DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('20 COMMIT; ERROR: update or delete on table "temporal_mltrng" violates foreign key constraint "tempora DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from ta -- -- FK between partitioned tables: ranges -- CREATE TABLE temporal_partitioned_rng ( id int4range, valid_at daterange, name text, CONSTRAINT temporal_paritioned_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 partition OF temporal_partitioned_rng FOR VALUES IN ('[1,2)', '[3,4)', '[5 CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6 INSERT INTO temporal_partitioned_rng (id, valid_at, name) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-01'), 'one'), ('[1,2)', daterange('2000-02-01', '2000-03-01'), 'one'), ('[2,3)', daterange('2000-01-01', '2010-01-01'), 'two'); CREATE TABLE temporal_partitioned_fk_rng2rng ( id int4range, valid_at daterange, parent_id int4range, CONSTRAINT temporal_partitioned_fk_rng2rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAP CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng (id, PERIOD valid_at) ) PARTITION BY LIST (id); CREATE TABLE tfkp1 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[1,2)', '[ CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[ -- -- partitioned FK referencing inserts -- INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[1,2)', daterange('2000-01-01', '2000-02-15'), '[1,2)'), ('[1,2)', daterange('2001-01-01', '2002-01-01'), '[2,3)'), ('[2,3)', daterange('2000-01-01', '2000-02-15'), '[1,2)'); -- should fail: INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2010-01-01', '2010-02-15'), '[1,2)'); ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitio DETAIL: Key (parent_id, valid_at)=([1,2), [2010-01-01,2010-02-15)) is not present in tabl INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2000-01-01', '2000-02-15'), '[3,4)'); ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitio DETAIL: Key (parent_id, valid_at)=([3,4), [2000-01-01,2000-02-15)) is not present in tabl -- -- partitioned FK referencing updates -- UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-02 -- move a row from the first partition to the second UPDATE temporal_partitioned_fk_rng2rng SET id = '[4,5)' WHERE id = '[1,2)'; -- move a row from the second partition to the first UPDATE temporal_partitioned_fk_rng2rng SET id = '[1,2)' WHERE id = '[4,5)'; -- should fail: UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-04 ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey" DETAIL: Key (id, valid_at)=([1,2), [2000-01-01,2000-04-01)) conflicts with existing key ( -- -- partitioned FK referenced updates NO ACTION -- TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2016-01 UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WH INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02 INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', d UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-02-01', '2016-03-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01'); -- should fail: UPDATE temporal_partitioned_rng SET valid_at = daterange('2016-01-01', '2016-02-01') WHERE id = '[5,6)' AND valid_at = daterange('2018-01-01', '2018-02-01'); ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitione DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from tabl -- -- partitioned FK referenced deletes NO ACTION -- TRUNCATE temporal_partitioned_rng, temporal_partitioned_fk_rng2rng; INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-01 INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02 INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', d DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02- -- should fail: DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-01- ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitione DETAIL: Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from tabl -- -- partitioned FK referenced updates CASCADE -- ALTER TABLE temporal_partitioned_fk_rng2rng DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE CASCADE ON UPDATE CASCADE; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- partitioned FK referenced deletes CASCADE -- -- -- partitioned FK referenced updates SET NULL -- ALTER TABLE temporal_partitioned_fk_rng2rng DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE SET NULL ON UPDATE SET NULL; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- partitioned FK referenced deletes SET NULL -- -- -- partitioned FK referenced updates SET DEFAULT -- ALTER TABLE temporal_partitioned_fk_rng2rng ALTER COLUMN parent_id SET DEFAULT '[-1,-1]', DROP CONSTRAINT temporal_partitioned_fk_rng2rng_fk, ADD CONSTRAINT temporal_partitioned_fk_rng2rng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_rng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- partitioned FK referenced deletes SET DEFAULT -- DROP TABLE temporal_partitioned_fk_rng2rng; DROP TABLE temporal_partitioned_rng; -- -- FK between partitioned tables: multiranges -- CREATE TABLE temporal_partitioned_mltrng ( id int4range, valid_at datemultirange, name text, CONSTRAINT temporal_paritioned_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) ) PARTITION BY LIST (id); CREATE TABLE tp1 PARTITION OF temporal_partitioned_mltrng FOR VALUES IN ('[1,2)', '[3,4)', CREATE TABLE tp2 PARTITION OF temporal_partitioned_mltrng FOR VALUES IN ('[0,1)', '[2,3)', INSERT INTO temporal_partitioned_mltrng (id, valid_at, name) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2000-02-01')), 'one'), ('[1,2)', datemultirange(daterange('2000-02-01', '2000-03-01')), 'one'), ('[2,3)', datemultirange(daterange('2000-01-01', '2010-01-01')), 'two'); CREATE TABLE temporal_partitioned_fk_mltrng2mltrng ( id int4range, valid_at datemultirange, parent_id int4range, CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_pk PRIMARY KEY (id, valid_at WITHOUT O CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD val REFERENCES temporal_partitioned_mltrng (id, PERIOD valid_at) ) PARTITION BY LIST (id); CREATE TABLE tfkp1 PARTITION OF temporal_partitioned_fk_mltrng2mltrng FOR VALUES IN ('[1, CREATE TABLE tfkp2 PARTITION OF temporal_partitioned_fk_mltrng2mltrng FOR VALUES IN ('[0, -- -- partitioned FK referencing inserts -- INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2000-02-15')), '[1,2)'), ('[1,2)', datemultirange(daterange('2001-01-01', '2002-01-01')), '[2,3)'), ('[2,3)', datemultirange(daterange('2000-01-01', '2000-02-15')), '[1,2)'); -- should fail: INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2010-01-01', '2010-02-15')), '[1,2)'); ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitio DETAIL: Key (parent_id, valid_at)=([1,2), {[2010-01-01,2010-02-15)}) is not present in ta INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2000-01-01', '2000-02-15')), '[3,4)'); ERROR: insert or update on table "tfkp1" violates foreign key constraint "temporal_partitio DETAIL: Key (parent_id, valid_at)=([3,4), {[2000-01-01,2000-02-15)}) is not present in ta -- -- partitioned FK referencing updates -- UPDATE temporal_partitioned_fk_mltrng2mltrng SET valid_at = datemultirange(daterange( -- move a row from the first partition to the second UPDATE temporal_partitioned_fk_mltrng2mltrng SET id = '[4,5)' WHERE id = '[1,2)'; -- move a row from the second partition to the first UPDATE temporal_partitioned_fk_mltrng2mltrng SET id = '[1,2)' WHERE id = '[4,5)'; -- should fail: UPDATE temporal_partitioned_fk_mltrng2mltrng SET valid_at = datemultirange(daterange( ERROR: conflicting key value violates exclusion constraint "tfkp1_pkey" DETAIL: Key (id, valid_at)=([1,2), {[2000-01-01,2000-04-01)}) conflicts with existing ke -- -- partitioned FK referenced updates NO ACTION -- TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange( UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2018-01-0 INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange( INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3 UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-02-0 WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01')); -- should fail: UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-01-0 WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')); ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitione DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from ta -- -- partitioned FK referenced deletes NO ACTION -- TRUNCATE temporal_partitioned_mltrng, temporal_partitioned_fk_mltrng2mltrng; INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange( INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange( INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3 DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(d -- should fail: DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(d ERROR: update or delete on table "tp1" violates foreign key constraint "temporal_partitione DETAIL: Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from ta -- -- partitioned FK referenced updates CASCADE -- ALTER TABLE temporal_partitioned_fk_mltrng2mltrng DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE CASCADE ON UPDATE CASCADE; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- partitioned FK referenced deletes CASCADE -- -- -- partitioned FK referenced updates SET NULL -- ALTER TABLE temporal_partitioned_fk_mltrng2mltrng DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE SET NULL ON UPDATE SET NULL; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- partitioned FK referenced deletes SET NULL -- -- -- partitioned FK referenced updates SET DEFAULT -- ALTER TABLE temporal_partitioned_fk_mltrng2mltrng ALTER COLUMN parent_id SET DEFAULT '[0,1)', DROP CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk, ADD CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at) REFERENCES temporal_partitioned_mltrng ON DELETE SET DEFAULT ON UPDATE SET DEFAULT; ERROR: unsupported ON UPDATE action for foreign key constraint using PERIOD -- -- partitioned FK referenced deletes SET DEFAULT -- DROP TABLE temporal_partitioned_fk_mltrng2mltrng; DROP TABLE temporal_partitioned_mltrng; RESET datestyle; [Verzeichnis aufwärts0.80unsichere VerbindungÜbersetzung europäischer Sprachen durch Browser2026-08-10] |
2026-08-25