0001-Revert-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-tempora.patch
text/plain
Filename: 0001-Revert-Fix-ON-CONFLICT-DO-NOTHING-UPDATE-for-tempora.patch
Type: text/plain
Part: 0
Message:
Re: SQL:2011 application time
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch 0001
Subject: Revert "Fix ON CONFLICT DO NOTHING/UPDATE for temporal indexes"
| File | + | − |
|---|---|---|
| src/backend/executor/execIndexing.c | 1 | 1 |
| src/backend/optimizer/util/plancat.c | 1 | 8 |
| src/test/regress/expected/without_overlaps.out | 0 | 176 |
| src/test/regress/sql/without_overlaps.sql | 0 | 113 |
From 771f847a2025aff96d083d7c21e2a2c3ae12f0c9 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 15 May 2024 10:28:28 +0200
Subject: [PATCH 01/10] Revert "Fix ON CONFLICT DO NOTHING/UPDATE for temporal
indexes"
This reverts commit 144c2ce0cc75ff99c66749b0ca5235d037df7c09.
---
src/backend/executor/execIndexing.c | 2 +-
src/backend/optimizer/util/plancat.c | 9 +-
.../regress/expected/without_overlaps.out | 176 ------------------
src/test/regress/sql/without_overlaps.sql | 113 -----------
4 files changed, 2 insertions(+), 298 deletions(-)
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index 59acf67a36a..9f05b3654c1 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -210,7 +210,7 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative)
* If the indexes are to be used for speculative insertion, add extra
* information required by unique index entries.
*/
- if (speculative && ii->ii_Unique && !indexDesc->rd_index->indisexclusion)
+ if (speculative && ii->ii_Unique)
BuildSpeculativeIndexInfo(indexDesc, ii);
relationDescs[i] = indexDesc;
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 26f8de77135..a51fc34e6e0 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -815,7 +815,7 @@ infer_arbiter_indexes(PlannerInfo *root)
*/
if (indexOidFromConstraint == idxForm->indexrelid)
{
- if (idxForm->indisexclusion && onconflict->action == ONCONFLICT_UPDATE)
+ if (!idxForm->indisunique && onconflict->action == ONCONFLICT_UPDATE)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints")));
@@ -840,13 +840,6 @@ infer_arbiter_indexes(PlannerInfo *root)
if (!idxForm->indisunique)
goto next;
- /*
- * So-called unique constraints with WITHOUT OVERLAPS are really
- * exclusion constraints, so skip those too.
- */
- if (idxForm->indisexclusion)
- goto next;
-
/* Build BMS representation of plain (non expression) index attrs */
indexedAttrs = NULL;
for (natt = 0; natt < idxForm->indnkeyatts; natt++)
diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out
index e2f2a1cbe20..abc22d0113f 100644
--- a/src/test/regress/expected/without_overlaps.out
+++ b/src/test/regress/expected/without_overlaps.out
@@ -453,182 +453,6 @@ DROP TABLE temporal_partitioned;
ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk;
ERROR: cannot use non-unique index "temporal_rng_pk" as replica identity
--
--- ON CONFLICT
---
-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;
---
-- test FK dependencies
--
-- can't drop a range referenced by an FK, unless with CASCADE
diff --git a/src/test/regress/sql/without_overlaps.sql b/src/test/regress/sql/without_overlaps.sql
index 5d41a6bd628..d4ae03ae529 100644
--- a/src/test/regress/sql/without_overlaps.sql
+++ b/src/test/regress/sql/without_overlaps.sql
@@ -319,119 +319,6 @@ CREATE TABLE tp2 PARTITION OF temporal_partitioned FOR VALUES IN ('[3,4)', '[4,5
-- (should fail)
ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk;
---
--- ON CONFLICT
---
-
-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;
-
-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;
--- 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;
--- 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;
-SELECT * FROM temporal_rng ORDER BY id, valid_at;
-
-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;
-
-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)';
--- 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)';
--- 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)';
-SELECT * FROM temporal_rng ORDER BY id, valid_at;
-
-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)';
--- 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)';
--- 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)';
-SELECT * FROM temporal_rng ORDER BY id, valid_at;
-
--- 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;
-
-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;
--- 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;
--- 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;
-SELECT * FROM temporal3 ORDER BY id, valid_at;
-
-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;
-
-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)';
--- 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)';
--- 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)';
-SELECT * FROM temporal3 ORDER BY id, valid_at;
-
-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)';
--- 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)';
--- 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)';
-SELECT * FROM temporal3 ORDER BY id, valid_at;
-
-DROP TABLE temporal3;
-
--
-- test FK dependencies
--
base-commit: 54cf0c51184fde6e4c01059be0080b5a60ce6802
--
2.44.0