From 46a0cd4cefb4d9b462d8cc4df5e7ecdd190bea92 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 24 Jan 2024 15:43:41 +0100 Subject: 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 Reviewed-by: Peter Eisentraut Reviewed-by: jian he Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com --- contrib/btree_gist/sql/without_overlaps.sql | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) create mode 100644 contrib/btree_gist/sql/without_overlaps.sql (limited to 'contrib/btree_gist/sql') diff --git a/contrib/btree_gist/sql/without_overlaps.sql b/contrib/btree_gist/sql/without_overlaps.sql new file mode 100644 index 00000000000..433c609ab22 --- /dev/null +++ b/contrib/btree_gist/sql/without_overlaps.sql @@ -0,0 +1,25 @@ +-- Core must test WITHOUT OVERLAPS +-- with an int4range + daterange, +-- so here we do some simple tests +-- to make sure int + daterange works too, +-- since that is the expected use-case. +CREATE TABLE temporal_rng ( + id integer, + valid_at daterange, + CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS) +); +\d temporal_rng +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'; + +INSERT INTO temporal_rng VALUES + (1, '[2000-01-01,2001-01-01)'); +-- same key, doesn't overlap: +INSERT INTO temporal_rng VALUES + (1, '[2001-01-01,2002-01-01)'); +-- overlaps but different key: +INSERT INTO temporal_rng VALUES + (2, '[2000-01-01,2001-01-01)'); +-- should fail: +INSERT INTO temporal_rng VALUES + (1, '[2000-06-01,2001-01-01)'); -- cgit v1.2.3