Add temporal PRIMARY KEY and UNIQUE constraints

Add WITHOUT OVERLAPS clause to PRIMARY KEY and UNIQUE constraints.
These are backed by GiST indexes instead of B-tree indexes, since they
are essentially exclusion constraints with = for the scalar parts of
the key and && for the temporal part.

Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com

3 files changed, 686 insertions, 1 deletions

diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
new file mode 100644
index 00000000000..726e94102bf
--- /dev/null
+++ b/src/test/regress/expected/without_overlaps.out
@@ -0,0 +1,395 @@
+-- Tests for WITHOUT OVERLAPS.
+--
+-- We leave behind several tables to test pg_dump etc:
+-- temporal_rng, temporal_rng2,
+-- temporal_fk_rng2rng.
+--
+-- test input parser
+--
+-- PK with no columns just WITHOUT OVERLAPS:
+CREATE TABLE temporal_rng (
+	valid_at tsrange,
+	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 WITHOU...
+         ^
+-- 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:  data type text has no default operator class for access method "gist"
+HINT:  You must specify an operator class for the index or define a default operator class for the data type.
+-- 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 tsrange,
+	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 | tsrange   |           | 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 with two columns plus a range:
+-- We don't drop this table because tests below also need multiple scalar columns.
+CREATE TABLE temporal_rng2 (
+	id1 int4range,
+	id2 int4range,
+	valid_at tsrange,
+	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 | tsrange   |           | 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 a multirange:
+CREATE TABLE temporal_mltrng (
+  id int4range,
+  valid_at tsmultirange,
+  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 | tsmultirange |           | not null | 
+Indexes:
+    "temporal_mltrng_pk" PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+
+-- UNIQUE with no columns just WITHOUT OVERLAPS:
+CREATE TABLE temporal_rng3 (
+	valid_at tsrange,
+	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 OV...
+         ^
+-- 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:  data type text has no default operator class for access method "gist"
+HINT:  You must specify an operator class for the index or define a default operator class for the data type.
+-- UNIQUE with one column plus a range:
+CREATE TABLE temporal_rng3 (
+	id int4range,
+	valid_at tsrange,
+	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 | tsrange   |           |          | 
+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 tsrange,
+	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 | tsrange   |           |          | 
+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 ALTER TABLE ADD CONSTRAINT
+--
+DROP TABLE temporal_rng;
+CREATE TABLE temporal_rng (
+	id int4range,
+	valid_at tsrange
+);
+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 tsrange
+);
+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 tsrange
+);
+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 tsrange
+);
+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 tsrange,
+	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 tsrange,
+	ADD CONSTRAINT temporal3_uq
+	UNIQUE (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+--
+-- test PK inserts
+--
+-- okay:
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[3,3]', tsrange('2018-01-01', NULL));
+-- should fail:
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05'));
+ERROR:  conflicting key value violates exclusion constraint "temporal_rng_pk"
+DETAIL:  Key (id, valid_at)=([1,2), ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018")) conflicts with existing key (id, valid_at)=([1,2), ["Tue Jan 02 00:00:00 2018","Sat Feb 03 00:00:00 2018")).
+INSERT INTO temporal_rng VALUES (NULL, tsrange('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, ["Mon Jan 01 00:00:00 2018","Fri Jan 05 00:00:00 2018")).
+INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
+ERROR:  null value in column "valid_at" of relation "temporal_rng" violates not-null constraint
+DETAIL:  Failing row contains ([3,4), null).
+--
+-- 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,1]', daterange('2000-01-01', '2010-01-01'), '[7,7]', 'foo'),
+  ('[2,2]', daterange('2000-01-01', '2010-01-01'), '[9,9]', 'bar')
+;
+DROP TABLE temporal3;
+--
+-- test changing the PK's dependencies
+--
+CREATE TABLE temporal3 (
+	id int4range,
+	valid_at tsrange,
+	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_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,1]', '[2,2]');
+CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,3]', '[4,4]');
+INSERT INTO temporal_partitioned VALUES
+  ('[1,1]', daterange('2000-01-01', '2000-02-01'), 'one'),
+  ('[1,1]', daterange('2000-02-01', '2000-03-01'), 'one'),
+  ('[3,3]', daterange('2000-01-01', '2010-01-01'), 'three');
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+  id   |        valid_at         | name  
+-------+-------------------------+-------
+ [1,2) | [01-01-2000,02-01-2000) | one
+ [1,2) | [02-01-2000,03-01-2000) | one
+ [3,4) | [01-01-2000,01-01-2010) | three
+(3 rows)
+
+SELECT * FROM tp1 ORDER BY id, valid_at;
+  id   |        valid_at         | name 
+-------+-------------------------+------
+ [1,2) | [01-01-2000,02-01-2000) | one
+ [1,2) | [02-01-2000,03-01-2000) | one
+(2 rows)
+
+SELECT * FROM tp2 ORDER BY id, valid_at;
+  id   |        valid_at         | name  
+-------+-------------------------+-------
+ [3,4) | [01-01-2000,01-01-2010) | three
+(1 row)
+
+DROP TABLE temporal_partitioned;
+-- temporal UNIQUE:
+CREATE TABLE temporal_partitioned (
+	id int4range,
+	valid_at daterange,
+  name text,
+	CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,1]', '[2,2]');
+CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,3]', '[4,4]');
+INSERT INTO temporal_partitioned VALUES
+  ('[1,1]', daterange('2000-01-01', '2000-02-01'), 'one'),
+  ('[1,1]', daterange('2000-02-01', '2000-03-01'), 'one'),
+  ('[3,3]', daterange('2000-01-01', '2010-01-01'), 'three');
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+  id   |        valid_at         | name  
+-------+-------------------------+-------
+ [1,2) | [01-01-2000,02-01-2000) | one
+ [1,2) | [02-01-2000,03-01-2000) | one
+ [3,4) | [01-01-2000,01-01-2010) | three
+(3 rows)
+
+SELECT * FROM tp1 ORDER BY id, valid_at;
+  id   |        valid_at         | name 
+-------+-------------------------+------
+ [1,2) | [01-01-2000,02-01-2000) | one
+ [1,2) | [02-01-2000,03-01-2000) | one
+(2 rows)
+
+SELECT * FROM tp2 ORDER BY id, valid_at;
+  id   |        valid_at         | name  
+-------+-------------------------+-------
+ [3,4) | [01-01-2000,01-01-2010) | three
+(1 row)
+
+DROP TABLE temporal_partitioned;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 6f5a33c234e..1d8a414eea7 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -78,7 +78,7 @@ test: brin_bloom brin_multi
 # psql depends on create_am
 # amutils depends on geometry, create_index_spgist, hash_index, brin
 # ----------
-test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role
+test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role without_overlaps
 
 # collate.*.utf8 tests cannot be run in parallel with each other
 test: rules psql psql_crosstab amutils stats_ext collate.linux.utf8 collate.windows.win1252
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
new file mode 100644
index 00000000000..c8e8ab994a0
--- /dev/null
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -0,0 +1,290 @@
+-- Tests for WITHOUT OVERLAPS.
+--
+-- We leave behind several tables to test pg_dump etc:
+-- temporal_rng, temporal_rng2,
+-- temporal_fk_rng2rng.
+
+--
+-- test input parser
+--
+
+-- PK with no columns just WITHOUT OVERLAPS:
+
+CREATE TABLE temporal_rng (
+	valid_at tsrange,
+	CONSTRAINT temporal_rng_pk PRIMARY KEY (valid_at WITHOUT OVERLAPS)
+);
+
+-- 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)
+);
+
+-- 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)
+);
+
+-- 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 tsrange,
+	CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng_pk';
+
+-- PK with two columns plus a range:
+-- We don't drop this table because tests below also need multiple scalar columns.
+CREATE TABLE temporal_rng2 (
+	id1 int4range,
+	id2 int4range,
+	valid_at tsrange,
+	CONSTRAINT temporal_rng2_pk PRIMARY KEY (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng2
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng2_pk';
+
+
+-- 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 a multirange:
+CREATE TABLE temporal_mltrng (
+  id int4range,
+  valid_at tsmultirange,
+  CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_mltrng
+
+-- UNIQUE with no columns just WITHOUT OVERLAPS:
+
+CREATE TABLE temporal_rng3 (
+	valid_at tsrange,
+	CONSTRAINT temporal_rng3_uq UNIQUE (valid_at WITHOUT OVERLAPS)
+);
+
+-- 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)
+);
+
+-- 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)
+);
+
+-- UNIQUE with one column plus a range:
+
+CREATE TABLE temporal_rng3 (
+	id int4range,
+	valid_at tsrange,
+	CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng3
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
+DROP TABLE temporal_rng3;
+
+-- UNIQUE with two columns plus a range:
+CREATE TABLE temporal_rng3 (
+	id1 int4range,
+	id2 int4range,
+	valid_at tsrange,
+	CONSTRAINT temporal_rng3_uq UNIQUE (id1, id2, valid_at WITHOUT OVERLAPS)
+);
+\d temporal_rng3
+SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
+SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_rng3_uq';
+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 ALTER TABLE ADD CONSTRAINT
+--
+
+DROP TABLE temporal_rng;
+CREATE TABLE temporal_rng (
+	id int4range,
+	valid_at tsrange
+);
+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 tsrange
+);
+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;
+DROP TABLE temporal3;
+
+-- UNIQUE with USING INDEX (not possible):
+CREATE TABLE temporal3 (
+	id int4range,
+	valid_at tsrange
+);
+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;
+DROP TABLE temporal3;
+
+-- UNIQUE with USING [UNIQUE] INDEX (possible but not a temporal constraint):
+CREATE TABLE temporal3 (
+	id int4range,
+	valid_at tsrange
+);
+CREATE UNIQUE INDEX idx_temporal3_uq ON temporal3 (id, valid_at);
+ALTER TABLE temporal3
+	ADD CONSTRAINT temporal3_uq
+	UNIQUE USING INDEX idx_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 tsrange,
+	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 tsrange,
+	ADD CONSTRAINT temporal3_uq
+	UNIQUE (id, valid_at WITHOUT OVERLAPS);
+DROP TABLE temporal3;
+
+--
+-- test PK inserts
+--
+
+-- okay:
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-02', '2018-02-03'));
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-03-03', '2018-04-04'));
+INSERT INTO temporal_rng VALUES ('[2,2]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[3,3]', tsrange('2018-01-01', NULL));
+
+-- should fail:
+INSERT INTO temporal_rng VALUES ('[1,1]', tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES (NULL, tsrange('2018-01-01', '2018-01-05'));
+INSERT INTO temporal_rng VALUES ('[3,3]', NULL);
+
+--
+-- 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,1]', daterange('2000-01-01', '2010-01-01'), '[7,7]', 'foo'),
+  ('[2,2]', daterange('2000-01-01', '2010-01-01'), '[9,9]', 'bar')
+;
+DROP TABLE temporal3;
+
+--
+-- test changing the PK's dependencies
+--
+
+CREATE TABLE temporal3 (
+	id int4range,
+	valid_at tsrange,
+	CONSTRAINT temporal3_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
+ALTER TABLE temporal3 ALTER COLUMN valid_at DROP NOT NULL;
+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_paritioned_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,1]', '[2,2]');
+CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,3]', '[4,4]');
+INSERT INTO temporal_partitioned VALUES
+  ('[1,1]', daterange('2000-01-01', '2000-02-01'), 'one'),
+  ('[1,1]', daterange('2000-02-01', '2000-03-01'), 'one'),
+  ('[3,3]', daterange('2000-01-01', '2010-01-01'), 'three');
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+SELECT * FROM tp1 ORDER BY id, valid_at;
+SELECT * FROM tp2 ORDER BY id, valid_at;
+DROP TABLE temporal_partitioned;
+
+-- temporal UNIQUE:
+CREATE TABLE temporal_partitioned (
+	id int4range,
+	valid_at daterange,
+  name text,
+	CONSTRAINT temporal_paritioned_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
+) PARTITION BY LIST (id);
+CREATE TABLE tp1 PARTITION OF temporal_partitioned FOR VALUES IN ('[1,1]', '[2,2]');
+CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,3]', '[4,4]');
+INSERT INTO temporal_partitioned VALUES
+  ('[1,1]', daterange('2000-01-01', '2000-02-01'), 'one'),
+  ('[1,1]', daterange('2000-02-01', '2000-03-01'), 'one'),
+  ('[3,3]', daterange('2000-01-01', '2010-01-01'), 'three');
+SELECT * FROM temporal_partitioned ORDER BY id, valid_at;
+SELECT * FROM tp1 ORDER BY id, valid_at;
+SELECT * FROM tp2 ORDER BY id, valid_at;
+DROP TABLE temporal_partitioned;