src/test/regress/expected/without_overlaps.out - master - githubmirror/postgres

src/test/regress/expected/without_overlaps.out
2 701 строка130 KB
Tom Lane
Fix WITHOUT OVERLAPS' interaction with domains.
07 апр 2026, 21:45
07 апр 2026, 21:454edd603
-- Tests for WITHOUT OVERLAPS.
--
-- We leave behind several tables to test pg_dump etc:
-- temporal_rng, temporal_rng2,
-- temporal_fk_rng2rng, temporal_fk2_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_rng_pk';
                                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_rng2_pk';
                                    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_mltrng_pk';
                                   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_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_mltrng2_pk';
                                       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_rng3_uq';
                                 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_rng3_uq';
                                    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), [1,13)).
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,2), [1,13)).
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 "temporal3_uq"
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-02-03'));
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05'));
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 OVERLAPS);
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-01-05'));
  ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
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_at)=([1,2), [2018-01-01,2018-01-05)).
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 OVERLAPS);
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 OVERLAPS);
DELETE FROM temporal_rng;
--
-- range PK: test inserts
--
-- okay:
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05'));
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-01-05'));
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 (id, valid_at)=([1,2), [2018-01-02,2018-02-03)).
INSERT INTO temporal_rng (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05'));
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 constraint
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 (id, valid_at)=([1,2), [2018-03-03,2018-04-04)).
-- 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 constraint
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-02-03'));
INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05'));
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-05'));
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 OVERLAPS);
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-01-05'));
  ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS);
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_at)=([1,2), [2018-01-01,2018-01-05)).
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 OVERLAPS);
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 OVERLAPS);
DELETE FROM temporal_rng3;
--
-- range UQ: test inserts
--
-- okay:
INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-01-02', '2018-02-03'));
INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[1,2)', daterange('2018-03-03', '2018-04-04'));
INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[2,3)', daterange('2018-01-01', '2018-01-05'));
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-05'));
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-01-05'));
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 (id, valid_at)=([1,2), [2018-01-02,2018-02-03)).
-- 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 (id, valid_at)=([1,2), [2018-03-03,2018-04-04)).
-- 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('2018-01-02', '2018-02-03')));
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
ALTER TABLE temporal_mltrng DROP CONSTRAINT temporal_mltrng_pk;
-- should fail:
BEGIN;
  INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
  ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS);
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, valid_at)=([1,2), {[2018-01-01,2018-01-05)}).
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 WITHOUT OVERLAPS);
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 WITHOUT OVERLAPS);
DELETE FROM temporal_mltrng;
--
-- multirange PK: test inserts
--
-- okay:
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
-- should fail:
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
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 key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}).
INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
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 constraint
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 key (id, valid_at)=([1,2), {[2018-03-03,2018-04-04)}).
-- 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 constraint
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('2018-01-02', '2018-02-03')));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', NULL);
ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS);
ALTER TABLE temporal_mltrng3 DROP CONSTRAINT temporal_mltrng3_uq;
-- should fail:
BEGIN;
  INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-01', '2018-01-05')));
  ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS);
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, valid_at)=([1,2), {[2018-01-01,2018-01-05)}).
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 WITHOUT OVERLAPS);
ERROR:  empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng3"
ROLLBACK;
ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS);
DELETE FROM temporal_mltrng3;
--
-- multirange UQ: test inserts
--
-- okay:
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-01-02', '2018-02-03')));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-03-03', '2018-04-04')));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2018-01-01', '2018-01-05')));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', datemultirange(daterange('2018-01-01', NULL)));
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05')));
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('2018-01-01', '2018-01-05')));
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 key (id, valid_at)=([1,2), {[2018-01-02,2018-02-03)}).
-- 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_mltrng3"
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 key (id, valid_at)=([1,2), {[2018-03-03,2018-04-04)}).
-- 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_mltrng3"
-- 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_mltrng3"
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')
;
UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-05-01' TO '2000-07-01'
  SET name = name || '1';
UPDATE temporal3 FOR PORTION OF valid_at FROM '2000-04-01' TO '2000-06-01'
  SET name = name || '2'
  WHERE id = '[2,3)';
SELECT * FROM temporal3 ORDER BY id, valid_at;
  id   |        valid_at         |  id2   | name  
-------+-------------------------+--------+-------
 [1,2) | [2000-01-01,2000-05-01) | [7,8)  | foo
 [1,2) | [2000-05-01,2000-07-01) | [7,8)  | foo1
 [1,2) | [2000-07-01,2010-01-01) | [7,8)  | foo
 [2,3) | [2000-01-01,2000-04-01) | [9,10) | bar
 [2,3) | [2000-04-01,2000-05-01) | [9,10) | bar2
 [2,3) | [2000-05-01,2000-06-01) | [9,10) | bar12
 [2,3) | [2000-06-01,2000-07-01) | [9,10) | bar1
 [2,3) | [2000-07-01,2010-01-01) | [9,10) | bar
(8 rows)

-- conflicting id only:
INSERT INTO temporal3 (id, valid_at, id2, name)
  VALUES
  ('[1,2)', daterange('2005-01-01', '2006-01-01'), '[8,9)', 'foo3');
ERROR:  conflicting key value violates exclusion constraint "temporal3_pk"
DETAIL:  Key (id, valid_at)=([1,2), [2005-01-01,2006-01-01)) conflicts with existing key (id, valid_at)=([1,2), [2000-07-01,2010-01-01)).
-- conflicting id2 only:
INSERT INTO temporal3 (id, valid_at, id2, name)
  VALUES
  ('[3,4)', daterange('2005-01-01', '2010-01-01'), '[9,10)', 'bar3');
ERROR:  conflicting key value violates exclusion constraint "temporal3_uniq"
DETAIL:  Key (id2, valid_at)=([9,10), [2005-01-01,2010-01-01)) conflicts with existing key (id2, valid_at)=([9,10), [2000-07-01,2010-01-01)).
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), upper(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_partitioned_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 tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
 tableoid |  id   |        valid_at         | name  
----------+-------+-------------------------+-------
 tp1      | [1,2) | [2000-01-01,2000-02-01) | one
 tp1      | [1,2) | [2000-02-01,2000-03-01) | one
 tp2      | [3,4) | [2000-01-01,2010-01-01) | three
(3 rows)

UPDATE  temporal_partitioned
  FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
  SET name = 'one2'
  WHERE id = '[1,2)';
UPDATE  temporal_partitioned
  FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
  SET id = '[4,5)'
  WHERE name = 'one';
UPDATE  temporal_partitioned
  FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
  SET id = '[2,3)'
  WHERE name = 'three';
DELETE FROM temporal_partitioned
  FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
  WHERE id = '[3,4)';
SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
 tableoid |  id   |        valid_at         | name  
----------+-------+-------------------------+-------
 tp1      | [1,2) | [2000-01-01,2000-01-15) | one
 tp1      | [1,2) | [2000-01-15,2000-02-01) | one2
 tp1      | [1,2) | [2000-02-01,2000-02-15) | one2
 tp1      | [1,2) | [2000-02-15,2000-02-20) | one
 tp1      | [1,2) | [2000-02-25,2000-03-01) | one
 tp1      | [2,3) | [2002-01-01,2003-01-01) | three
 tp2      | [3,4) | [2000-01-01,2000-01-15) | three
 tp2      | [3,4) | [2000-02-15,2002-01-01) | three
 tp2      | [3,4) | [2003-01-01,2010-01-01) | three
 tp2      | [4,5) | [2000-02-20,2000-02-25) | one
(10 rows)

DROP TABLE temporal_partitioned;
-- temporal UNIQUE:
CREATE TABLE temporal_partitioned (
  id int4range,
  valid_at daterange,
  name text,
  CONSTRAINT temporal_partitioned_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 tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
 tableoid |  id   |        valid_at         | name  
----------+-------+-------------------------+-------
 tp1      | [1,2) | [2000-01-01,2000-02-01) | one
 tp1      | [1,2) | [2000-02-01,2000-03-01) | one
 tp2      | [3,4) | [2000-01-01,2010-01-01) | three
(3 rows)

UPDATE  temporal_partitioned
  FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
  SET name = 'one2'
  WHERE id = '[1,2)';
UPDATE  temporal_partitioned
  FOR PORTION OF valid_at FROM '2000-02-20' TO '2000-02-25'
  SET id = '[4,5)'
  WHERE name = 'one';
UPDATE  temporal_partitioned
  FOR PORTION OF valid_at FROM '2002-01-01' TO '2003-01-01'
  SET id = '[2,3)'
  WHERE name = 'three';
DELETE FROM temporal_partitioned
  FOR PORTION OF valid_at FROM '2000-01-15' TO '2000-02-15'
  WHERE id = '[3,4)';
SELECT tableoid::regclass, * FROM temporal_partitioned ORDER BY id, valid_at;
 tableoid |  id   |        valid_at         | name  
----------+-------+-------------------------+-------
 tp1      | [1,2) | [2000-01-01,2000-01-15) | one
 tp1      | [1,2) | [2000-01-15,2000-02-01) | one2
 tp1      | [1,2) | [2000-02-01,2000-02-15) | one2
 tp1      | [1,2) | [2000-02-15,2000-02-20) | one
 tp1      | [1,2) | [2000-02-25,2000-03-01) | one
 tp1      | [2,3) | [2002-01-01,2003-01-01) | three
 tp2      | [3,4) | [2000-01-01,2000-01-15) | three
 tp2      | [3,4) | [2000-02-15,2002-01-01) | three
 tp2      | [3,4) | [2003-01-01,2010-01-01) | three
 tp2      | [4,5) | [2000-02-20,2000-02-25) | one
(10 rows)

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-01-01'));
-- with a conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING;
-- id matches but no conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT DO NOTHING;
-- date matches but no conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING;
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-01-01'));
-- with a conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
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-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
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-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
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-01-01'));
-- with a conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING;
-- id matches but no conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING;
-- date matches but no conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO NOTHING;
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-01-01'));
-- with a conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[2,3)';
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-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[3,4)';
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-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)';
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-01-01'));
-- with a conflict
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[2,3)';
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-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[3,4)';
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-01-01')) ON CONFLICT ON CONSTRAINT temporal_rng_pk DO UPDATE SET id = EXCLUDED.id + '[4,5)';
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-01'));
-- with a conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING;
-- id matches but no conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT DO NOTHING;
-- date matches but no conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING;
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-01'));
-- with a conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
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-01')) ON CONFLICT (id, valid_at) DO NOTHING;
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-01')) ON CONFLICT (id, valid_at) DO NOTHING;
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-01'));
-- with a conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING;
-- id matches but no conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING;
-- date matches but no conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING;
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-01'));
-- with a conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[2,3)';
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-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[3,4)';
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-01')) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)';
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-01'));
-- with a conflict
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[2,3)';
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-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[3,4)';
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-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO UPDATE SET id = EXCLUDED.id + '[4,5)';
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING;
-- id matches but no conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT DO NOTHING;
-- date matches but no conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING;
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING;
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('2010-01-01', '2020-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING;
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('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING;
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING;
-- id matches but no conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING;
-- date matches but no conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING;
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[2,3)';
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('2010-01-01', '2020-01-01'))) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[3,4)';
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('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)';
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO UPDATE SET id = EXCLUDED.id + '[2,3)';
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('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO UPDATE SET id = EXCLUDED.id + '[3,4)';
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('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO UPDATE SET id = EXCLUDED.id + '[4,5)';
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING;
-- id matches but no conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT DO NOTHING;
-- date matches but no conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING;
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING;
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('2010-01-01', '2020-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING;
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('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING;
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING;
-- id matches but no conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING;
-- date matches but no conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING;
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[2,3)';
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('2010-01-01', '2020-01-01'))) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[3,4)';
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('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)';
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('2000-01-01', '2010-01-01')));
-- with a conflict
INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO UPDATE SET id = EXCLUDED.id + '[2,3)';
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('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO UPDATE SET id = EXCLUDED.id + '[3,4)';
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('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO UPDATE SET id = EXCLUDED.id + '[4,5)';
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 valid_at of table temporal3
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 table are of incompatible types: int4range and daterange.
-- 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) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at)

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) REFERENCES temporal_rng2(id1, id2, PERIOD valid_at)

-- 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 table are of incompatible types: tsrange and daterange.
ALTER TABLE temporal_fk_rng2rng
  ALTER COLUMN valid_at TYPE daterange USING daterange(lower(valid_at)::date, upper(valid_at)::date);
-- 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('2018-01-02', '2018-02-01'), '[1,2)');
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('2018-01-02', '2018-04-01'), '[1,2)');
-- 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 "temporal_fk_rng2rng_fk"
DETAIL:  Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-04-01)) is not present in table "temporal_rng".
-- 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_rng2rng_fk';
                                 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('2018-01-02', '2018-02-01'), '[1,2)');
-- should fail:
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)');
ERROR:  insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk"
DETAIL:  Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-04-01)) is not present in table "temporal_rng".
-- now it should work:
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2018-02-03', '2018-03-03'));
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[2,3)', daterange('2018-01-02', '2018-04-01'), '[1,2)');
--
-- 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 = '[1,2)';
-- should fail:
UPDATE temporal_fk_rng2rng SET valid_at = daterange('2018-01-02', '2018-05-01') WHERE id = '[1,2)';
ERROR:  insert or update on table "temporal_fk_rng2rng" violates foreign key constraint "temporal_fk_rng2rng_fk"
DETAIL:  Key (parent_id, valid_at)=([1,2), [2018-01-02,2018-05-01)) is not present in table "temporal_rng".
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 "temporal_fk_rng2rng_fk"
DETAIL:  Key (parent_id, valid_at)=([8,9), [2018-01-02,2018-02-20)) is not present in table "temporal_rng".
-- 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_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
--
-- 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-02-01'));
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-01-05')
                      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 (error):
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_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from table "temporal_fk_rng2rng".
-- 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_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([1,2), [2018-01-01,2018-03-01)) is still referenced from table "temporal_fk_rng2rng".
-- 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_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
-- 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_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
-- 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_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
-- changing an unreferenced part is okay:
UPDATE temporal_rng
  FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
  SET id = '[7,8)'
  WHERE id = '[5,6)';
-- changing just a part fails:
UPDATE temporal_rng
  FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
  SET id = '[7,8)'
  WHERE id = '[5,6)';
ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
  id   |        valid_at         
-------+-------------------------
 [5,6) | [2016-02-01,2016-03-01)
 [5,6) | [2018-01-01,2018-01-02)
 [5,6) | [2018-01-03,2018-02-01)
 [7,8) | [2018-01-02,2018-01-03)
(4 rows)

SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
  id   |        valid_at         | parent_id 
-------+-------------------------+-----------
 [3,4) | [2018-01-05,2018-01-10) | [5,6)
(1 row)

-- 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-02-01'));
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-01');
-- 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-01');
ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
-- 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-01');
COMMIT;
ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
-- deleting an unreferenced part is okay:
DELETE FROM temporal_rng
FOR PORTION OF valid_at FROM '2018-01-02' TO '2018-01-03'
WHERE id = '[5,6)';
SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
  id   |        valid_at         
-------+-------------------------
 [5,6) | [2018-01-01,2018-01-02)
 [5,6) | [2018-01-03,2018-02-01)
(2 rows)

SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
  id   |        valid_at         | parent_id 
-------+-------------------------+-----------
 [3,4) | [2018-01-05,2018-01-10) | [5,6)
(1 row)

-- deleting just a part fails:
DELETE FROM temporal_rng
FOR PORTION OF valid_at FROM '2018-01-05' TO '2018-01-10'
WHERE id = '[5,6)';
ERROR:  update or delete on table "temporal_rng" violates foreign key constraint "temporal_fk_rng2rng_fk" on table "temporal_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-03,2018-02-01)) is still referenced from table "temporal_fk_rng2rng".
SELECT * FROM temporal_rng WHERE id in ('[5,6)', '[7,8)') ORDER BY id, valid_at;
  id   |        valid_at         
-------+-------------------------
 [5,6) | [2018-01-01,2018-01-02)
 [5,6) | [2018-01-03,2018-02-01)
(2 rows)

SELECT * FROM temporal_fk_rng2rng WHERE id in ('[3,4)') ORDER BY id, valid_at;
  id   |        valid_at         | parent_id 
-------+-------------------------+-----------
 [3,4) | [2018-01-05,2018-01-10) | [5,6)
(1 row)

-- 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-01');
--
-- 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
--
-- rng2rng test ON UPDATE/DELETE options
--
-- test FK referenced updates CASCADE
TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
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
TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
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
TRUNCATE temporal_rng, temporal_fk_rng2rng;
INSERT INTO temporal_rng (id, valid_at) VALUES ('[-1,-1]', daterange(null, null));
INSERT INTO temporal_rng (id, valid_at) VALUES ('[6,7)', daterange('2018-01-01', '2021-01-01'));
INSERT INTO temporal_fk_rng2rng (id, valid_at, parent_id) VALUES ('[100,101)', daterange('2018-01-01', '2021-01-01'), '[6,7)');
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 table are of incompatible types: int4multirange and datemultirange.
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 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) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at)

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) REFERENCES temporal_mltrng2(id1, id2, PERIOD valid_at)

-- 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)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)');
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)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
-- 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 constraint "temporal_fk_mltrng2mltrng_fk"
DETAIL:  Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in table "temporal_mltrng".
-- 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_mltrng2mltrng_fk';
                                   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)', datemultirange(daterange('2018-01-02', '2018-02-01')), '[1,2)');
-- should fail:
INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
ERROR:  insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk"
DETAIL:  Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-04-01)}) is not present in table "temporal_mltrng".
-- now it should work:
INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2018-02-03', '2018-03-03')));
INSERT INTO temporal_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[2,3)', datemultirange(daterange('2018-01-02', '2018-04-01')), '[1,2)');
--
-- test FK referencing updates
--
-- slide the edge across a referenced transition:
UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-02-20')) WHERE id = '[1,2)';
-- should fail:
UPDATE temporal_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2018-01-02', '2018-05-01')) WHERE id = '[1,2)';
ERROR:  insert or update on table "temporal_fk_mltrng2mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk"
DETAIL:  Key (parent_id, valid_at)=([1,2), {[2018-01-02,2018-05-01)}) is not present in table "temporal_mltrng".
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 constraint "temporal_fk_mltrng2mltrng_fk"
DETAIL:  Key (parent_id, valid_at)=([8,9), {[2018-01-02,2018-02-20)}) is not present in table "temporal_mltrng".
-- 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 "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
--
-- 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('2018-01-01', '2018-02-01')));
UPDATE temporal_mltrng SET valid_at = datemultirange(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_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-01'))
  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('2018-01-01', '2018-01-05'))
                      WHEN lower(valid_at) = '2018-02-01' THEN datemultirange(daterange('2018-01-05', '2018-03-01')) END
  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-01'))
  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-01'))
  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 multirange (error):
UPDATE temporal_mltrng SET id = '[2,3)', valid_at = datemultirange(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_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- 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 "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([1,2), {[2018-01-01,2018-03-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- 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-15'))
  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-01'))
  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 "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- 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-01'))
  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 "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- 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 "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- changing an unreferenced part is okay:
UPDATE temporal_mltrng
  FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
  SET id = '[7,8)'
  WHERE id = '[5,6)';
-- changing just a part fails:
UPDATE temporal_mltrng
  FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
  SET id = '[7,8)'
  WHERE id = '[5,6)';
ERROR:  update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- then delete the objecting FK record and the same PK update succeeds:
DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
UPDATE temporal_mltrng SET id = '[7,8)'
  WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
--
-- 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('2018-01-01', '2018-02-01')));
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)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01'));
-- a PK delete that fails because both are referenced:
DELETE FROM temporal_mltrng 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 "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- 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('2018-01-01', '2018-02-01'));
COMMIT;
ERROR:  update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- deleting an unreferenced part is okay:
DELETE FROM temporal_mltrng
FOR PORTION OF valid_at (datemultirange(daterange('2018-01-02', '2018-01-03')))
WHERE id = '[5,6)';
-- deleting just a part fails:
DELETE FROM temporal_mltrng
FOR PORTION OF valid_at (datemultirange(daterange('2018-01-05', '2018-01-10')))
WHERE id = '[5,6)';
ERROR:  update or delete on table "temporal_mltrng" violates foreign key constraint "temporal_fk_mltrng2mltrng_fk" on table "temporal_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-01-02),[2018-01-03,2018-02-01)}) is still referenced from table "temporal_fk_mltrng2mltrng".
-- then delete the objecting FK record and the same PK delete succeeds:
DELETE FROM temporal_fk_mltrng2mltrng WHERE id = '[3,4)';
DELETE FROM temporal_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-01-01', '2018-02-01'));
--
-- FK between partitioned tables: ranges
--
CREATE TABLE temporal_partitioned_rng (
  id int4range,
  valid_at daterange,
  name text,
  CONSTRAINT temporal_partitioned_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,6)', '[7,8)', '[9,10)', '[11,12)');
CREATE TABLE tp2 partition OF temporal_partitioned_rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)');
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 OVERLAPS),
  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)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)');
CREATE TABLE tfkp2 partition OF temporal_partitioned_fk_rng2rng FOR VALUES IN ('[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)');
--
-- 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_partitioned_fk_rng2rng_fk"
DETAIL:  Key (parent_id, valid_at)=([1,2), [2010-01-01,2010-02-15)) is not present in table "temporal_partitioned_rng".
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_partitioned_fk_rng2rng_fk"
DETAIL:  Key (parent_id, valid_at)=([3,4), [2000-01-01,2000-02-15)) is not present in table "temporal_partitioned_rng".
--
-- partitioned FK referencing updates
--
UPDATE temporal_partitioned_fk_rng2rng SET valid_at = daterange('2000-01-01', '2000-02-13') WHERE id = '[2,3)';
-- 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-01') WHERE id = '[1,2)';
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 (id, valid_at)=([1,2), [2000-01-01,2000-04-01)).
--
-- 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-01', '2016-02-01'));
UPDATE temporal_partitioned_rng SET valid_at = daterange('2018-01-01', '2018-02-01') WHERE id = '[5,6)';
INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
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_partitioned_fk_rng2rng_fk_1" on table "temporal_partitioned_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng".
--
-- 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-01', '2018-02-01'));
INSERT INTO temporal_partitioned_rng (id, valid_at) VALUES ('[5,6)', daterange('2018-02-01', '2018-03-01'));
INSERT INTO temporal_partitioned_fk_rng2rng (id, valid_at, parent_id) VALUES ('[3,4)', daterange('2018-01-05', '2018-01-10'), '[5,6)');
DELETE FROM temporal_partitioned_rng WHERE id = '[5,6)' AND valid_at = daterange('2018-02-01', '2018-03-01');
-- should fail:
DELETE FROM temporal_partitioned_rng 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_partitioned_fk_rng2rng_fk_1" on table "temporal_partitioned_fk_rng2rng"
DETAIL:  Key (id, valid_at)=([5,6), [2018-01-01,2018-02-01)) is still referenced from table "temporal_partitioned_fk_rng2rng".
--
-- 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_partitioned_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)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)');
CREATE TABLE tp2 PARTITION OF temporal_partitioned_mltrng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)');
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 OVERLAPS),
  CONSTRAINT temporal_partitioned_fk_mltrng2mltrng_fk FOREIGN KEY (parent_id, PERIOD valid_at)
    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,2)', '[3,4)', '[5,6)', '[7,8)', '[9,10)', '[11,12)', '[13,14)', '[15,16)', '[17,18)', '[19,20)', '[21,22)', '[23,24)');
CREATE TABLE tfkp2 PARTITION OF temporal_partitioned_fk_mltrng2mltrng FOR VALUES IN ('[0,1)', '[2,3)', '[4,5)', '[6,7)', '[8,9)', '[10,11)', '[12,13)', '[14,15)', '[16,17)', '[18,19)', '[20,21)', '[22,23)', '[24,25)');
--
-- 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_partitioned_fk_mltrng2mltrng_fk"
DETAIL:  Key (parent_id, valid_at)=([1,2), {[2010-01-01,2010-02-15)}) is not present in table "temporal_partitioned_mltrng".
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_partitioned_fk_mltrng2mltrng_fk"
DETAIL:  Key (parent_id, valid_at)=([3,4), {[2000-01-01,2000-02-15)}) is not present in table "temporal_partitioned_mltrng".
--
-- partitioned FK referencing updates
--
UPDATE temporal_partitioned_fk_mltrng2mltrng SET valid_at = datemultirange(daterange('2000-01-01', '2000-02-13')) WHERE id = '[2,3)';
-- 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('2000-01-01', '2000-04-01')) WHERE id = '[1,2)';
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 (id, valid_at)=([1,2), {[2000-01-01,2000-04-01)}).
--
-- 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(daterange('2016-01-01', '2016-02-01')));
UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2018-01-01', '2018-02-01')) WHERE id = '[5,6)';
INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01')));
INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
UPDATE temporal_partitioned_mltrng SET valid_at = datemultirange(daterange('2016-02-01', '2016-03-01'))
  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-01', '2016-02-01'))
  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_partitioned_fk_mltrng2mltrng_fk_2" on table "temporal_partitioned_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_partitioned_fk_mltrng2mltrng".
--
-- 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(daterange('2018-01-01', '2018-02-01')));
INSERT INTO temporal_partitioned_mltrng (id, valid_at) VALUES ('[5,6)', datemultirange(daterange('2018-02-01', '2018-03-01')));
INSERT INTO temporal_partitioned_fk_mltrng2mltrng (id, valid_at, parent_id) VALUES ('[3,4)', datemultirange(daterange('2018-01-05', '2018-01-10')), '[5,6)');
DELETE FROM temporal_partitioned_mltrng WHERE id = '[5,6)' AND valid_at = datemultirange(daterange('2018-02-01', '2018-03-01'));
-- should fail:
DELETE FROM temporal_partitioned_mltrng 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_partitioned_fk_mltrng2mltrng_fk_2" on table "temporal_partitioned_fk_mltrng2mltrng"
DETAIL:  Key (id, valid_at)=([5,6), {[2018-01-01,2018-02-01)}) is still referenced from table "temporal_partitioned_fk_mltrng2mltrng".
--
-- 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;