Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit144c2ce

Browse files
committed
Fix ON CONFLICT DO NOTHING/UPDATE for temporal indexes
A PRIMARY KEY or UNIQUE constraint with WITHOUT OVERLAPS will be aGiST index, not a B-Tree, but it will still have indisunique set. Thecode for ON CONFLICT fails if it sees a non-btree index that hasindisunique. This commit fixes that and adds some tests. But nowthat we can't just test indisunique, we also need some extra checks toprevent DO UPDATE from running against a WITHOUT OVERLAPS constraint(because the conflict could happen against more than one row, and we'donly update one).Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>Discussion:https://www.postgresql.org/message-id/1426589a-83cb-4a89-bf40-713970c07e63@illuminatedcomputing.com
1 parent5caa057 commit144c2ce

File tree

4 files changed

+298
-2
lines changed

4 files changed

+298
-2
lines changed

‎src/backend/executor/execIndexing.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -210,7 +210,7 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative)
210210
* If the indexes are to be used for speculative insertion, add extra
211211
* information required by unique index entries.
212212
*/
213-
if (speculative&&ii->ii_Unique)
213+
if (speculative&&ii->ii_Unique&& !indexDesc->rd_index->indisexclusion)
214214
BuildSpeculativeIndexInfo(indexDesc,ii);
215215

216216
relationDescs[i]=indexDesc;

‎src/backend/optimizer/util/plancat.c

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -815,7 +815,7 @@ infer_arbiter_indexes(PlannerInfo *root)
815815
*/
816816
if (indexOidFromConstraint==idxForm->indexrelid)
817817
{
818-
if (!idxForm->indisunique&&onconflict->action==ONCONFLICT_UPDATE)
818+
if (idxForm->indisexclusion&&onconflict->action==ONCONFLICT_UPDATE)
819819
ereport(ERROR,
820820
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
821821
errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints")));
@@ -840,6 +840,13 @@ infer_arbiter_indexes(PlannerInfo *root)
840840
if (!idxForm->indisunique)
841841
gotonext;
842842

843+
/*
844+
* So-called unique constraints with WITHOUT OVERLAPS are really
845+
* exclusion constraints, so skip those too.
846+
*/
847+
if (idxForm->indisexclusion)
848+
gotonext;
849+
843850
/* Build BMS representation of plain (non expression) index attrs */
844851
indexedAttrs=NULL;
845852
for (natt=0;natt<idxForm->indnkeyatts;natt++)

‎src/test/regress/expected/without_overlaps.out

Lines changed: 176 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -453,6 +453,182 @@ DROP TABLE temporal_partitioned;
453453
ALTER TABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk;
454454
ERROR: cannot use non-unique index "temporal_rng_pk" as replica identity
455455
--
456+
-- ON CONFLICT
457+
--
458+
TRUNCATE temporal_rng;
459+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
460+
-- with a conflict
461+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING;
462+
-- id matches but no conflict
463+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT DO NOTHING;
464+
-- date matches but no conflict
465+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING;
466+
SELECT * FROM temporal_rng ORDER BY id, valid_at;
467+
id | valid_at
468+
-------+-------------------------
469+
[1,2) | [2000-01-01,2010-01-01)
470+
[1,2) | [2010-01-01,2020-01-01)
471+
[2,3) | [2005-01-01,2006-01-01)
472+
(3 rows)
473+
474+
TRUNCATE temporal_rng;
475+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
476+
-- with a conflict
477+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
478+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
479+
-- id matches but no conflict
480+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
481+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
482+
-- date matches but no conflict
483+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
484+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
485+
SELECT * FROM temporal_rng ORDER BY id, valid_at;
486+
id | valid_at
487+
-------+-------------------------
488+
[1,2) | [2000-01-01,2010-01-01)
489+
(1 row)
490+
491+
TRUNCATE temporal_rng;
492+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
493+
-- with a conflict
494+
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;
495+
-- id matches but no conflict
496+
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;
497+
-- date matches but no conflict
498+
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;
499+
SELECT * FROM temporal_rng ORDER BY id, valid_at;
500+
id | valid_at
501+
-------+-------------------------
502+
[1,2) | [2000-01-01,2010-01-01)
503+
[1,2) | [2010-01-01,2020-01-01)
504+
[2,3) | [2005-01-01,2006-01-01)
505+
(3 rows)
506+
507+
TRUNCATE temporal_rng;
508+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
509+
-- with a conflict
510+
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)';
511+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
512+
-- id matches but no conflict
513+
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)';
514+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
515+
-- date matches but no conflict
516+
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)';
517+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
518+
SELECT * FROM temporal_rng ORDER BY id, valid_at;
519+
id | valid_at
520+
-------+-------------------------
521+
[1,2) | [2000-01-01,2010-01-01)
522+
(1 row)
523+
524+
TRUNCATE temporal_rng;
525+
INSERT INTO temporal_rng (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
526+
-- with a conflict
527+
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)';
528+
ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
529+
-- id matches but no conflict
530+
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)';
531+
ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
532+
-- date matches but no conflict
533+
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)';
534+
ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
535+
SELECT * FROM temporal_rng ORDER BY id, valid_at;
536+
id | valid_at
537+
-------+-------------------------
538+
[1,2) | [2000-01-01,2010-01-01)
539+
(1 row)
540+
541+
-- with a UNIQUE constraint:
542+
CREATE TABLE temporal3 (
543+
id int4range,
544+
valid_at daterange,
545+
CONSTRAINT temporal3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
546+
);
547+
TRUNCATE temporal3;
548+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
549+
-- with a conflict
550+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING;
551+
-- id matches but no conflict
552+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT DO NOTHING;
553+
-- date matches but no conflict
554+
INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT DO NOTHING;
555+
SELECT * FROM temporal3 ORDER BY id, valid_at;
556+
id | valid_at
557+
-------+-------------------------
558+
[1,2) | [2000-01-01,2010-01-01)
559+
[1,2) | [2010-01-01,2020-01-01)
560+
[2,3) | [2005-01-01,2006-01-01)
561+
(3 rows)
562+
563+
TRUNCATE temporal3;
564+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
565+
-- with a conflict
566+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
567+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
568+
-- id matches but no conflict
569+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
570+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
571+
-- date matches but no conflict
572+
INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT (id, valid_at) DO NOTHING;
573+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
574+
SELECT * FROM temporal3 ORDER BY id, valid_at;
575+
id | valid_at
576+
-------+-------------------------
577+
[1,2) | [2000-01-01,2010-01-01)
578+
(1 row)
579+
580+
TRUNCATE temporal3;
581+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
582+
-- with a conflict
583+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING;
584+
-- id matches but no conflict
585+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2010-01-01', '2020-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING;
586+
-- date matches but no conflict
587+
INSERT INTO temporal3 (id, valid_at) VALUES ('[2,3)', daterange('2005-01-01', '2006-01-01')) ON CONFLICT ON CONSTRAINT temporal3_uq DO NOTHING;
588+
SELECT * FROM temporal3 ORDER BY id, valid_at;
589+
id | valid_at
590+
-------+-------------------------
591+
[1,2) | [2000-01-01,2010-01-01)
592+
[1,2) | [2010-01-01,2020-01-01)
593+
[2,3) | [2005-01-01,2006-01-01)
594+
(3 rows)
595+
596+
TRUNCATE temporal3;
597+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
598+
-- with a conflict
599+
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)';
600+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
601+
-- id matches but no conflict
602+
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)';
603+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
604+
-- date matches but no conflict
605+
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)';
606+
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
607+
SELECT * FROM temporal3 ORDER BY id, valid_at;
608+
id | valid_at
609+
-------+-------------------------
610+
[1,2) | [2000-01-01,2010-01-01)
611+
(1 row)
612+
613+
TRUNCATE temporal3;
614+
INSERT INTO temporal3 (id, valid_at) VALUES ('[1,2)', daterange('2000-01-01', '2010-01-01'));
615+
-- with a conflict
616+
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)';
617+
ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
618+
-- id matches but no conflict
619+
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)';
620+
ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
621+
-- date matches but no conflict
622+
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)';
623+
ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints
624+
SELECT * FROM temporal3 ORDER BY id, valid_at;
625+
id | valid_at
626+
-------+-------------------------
627+
[1,2) | [2000-01-01,2010-01-01)
628+
(1 row)
629+
630+
DROP TABLE temporal3;
631+
--
456632
-- test FK dependencies
457633
--
458634
-- can't drop a range referenced by an FK, unless with CASCADE

‎src/test/regress/sql/without_overlaps.sql

Lines changed: 113 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -319,6 +319,119 @@ DROP TABLE temporal_partitioned;
319319
-- (should fail)
320320
ALTERTABLE temporal_rng REPLICA IDENTITY USING INDEX temporal_rng_pk;
321321

322+
--
323+
-- ON CONFLICT
324+
--
325+
326+
TRUNCATE temporal_rng;
327+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
328+
-- with a conflict
329+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICT DO NOTHING;
330+
-- id matches but no conflict
331+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICT DO NOTHING;
332+
-- date matches but no conflict
333+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICT DO NOTHING;
334+
SELECT*FROM temporal_rngORDER BY id, valid_at;
335+
336+
TRUNCATE temporal_rng;
337+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
338+
-- with a conflict
339+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICT (id, valid_at) DO NOTHING;
340+
-- id matches but no conflict
341+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICT (id, valid_at) DO NOTHING;
342+
-- date matches but no conflict
343+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICT (id, valid_at) DO NOTHING;
344+
SELECT*FROM temporal_rngORDER BY id, valid_at;
345+
346+
TRUNCATE temporal_rng;
347+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
348+
-- with a conflict
349+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICTONCONSTRAINT temporal_rng_pk DO NOTHING;
350+
-- id matches but no conflict
351+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICTONCONSTRAINT temporal_rng_pk DO NOTHING;
352+
-- date matches but no conflict
353+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICTONCONSTRAINT temporal_rng_pk DO NOTHING;
354+
SELECT*FROM temporal_rngORDER BY id, valid_at;
355+
356+
TRUNCATE temporal_rng;
357+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
358+
-- with a conflict
359+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICT (id, valid_at) DOUPDATESET id=EXCLUDED.id+'[2,3)';
360+
-- id matches but no conflict
361+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICT (id, valid_at) DOUPDATESET id=EXCLUDED.id+'[3,4)';
362+
-- date matches but no conflict
363+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICT (id, valid_at) DOUPDATESET id=EXCLUDED.id+'[4,5)';
364+
SELECT*FROM temporal_rngORDER BY id, valid_at;
365+
366+
TRUNCATE temporal_rng;
367+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
368+
-- with a conflict
369+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICTONCONSTRAINT temporal_rng_pk DOUPDATESET id=EXCLUDED.id+'[2,3)';
370+
-- id matches but no conflict
371+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICTONCONSTRAINT temporal_rng_pk DOUPDATESET id=EXCLUDED.id+'[3,4)';
372+
-- date matches but no conflict
373+
INSERT INTO temporal_rng (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICTONCONSTRAINT temporal_rng_pk DOUPDATESET id=EXCLUDED.id+'[4,5)';
374+
SELECT*FROM temporal_rngORDER BY id, valid_at;
375+
376+
-- with a UNIQUE constraint:
377+
378+
CREATETABLEtemporal3 (
379+
id int4range,
380+
valid_at daterange,
381+
CONSTRAINT temporal3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS)
382+
);
383+
TRUNCATE temporal3;
384+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
385+
-- with a conflict
386+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICT DO NOTHING;
387+
-- id matches but no conflict
388+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICT DO NOTHING;
389+
-- date matches but no conflict
390+
INSERT INTO temporal3 (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICT DO NOTHING;
391+
SELECT*FROM temporal3ORDER BY id, valid_at;
392+
393+
TRUNCATE temporal3;
394+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
395+
-- with a conflict
396+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICT (id, valid_at) DO NOTHING;
397+
-- id matches but no conflict
398+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICT (id, valid_at) DO NOTHING;
399+
-- date matches but no conflict
400+
INSERT INTO temporal3 (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICT (id, valid_at) DO NOTHING;
401+
SELECT*FROM temporal3ORDER BY id, valid_at;
402+
403+
TRUNCATE temporal3;
404+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
405+
-- with a conflict
406+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICTONCONSTRAINT temporal3_uq DO NOTHING;
407+
-- id matches but no conflict
408+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICTONCONSTRAINT temporal3_uq DO NOTHING;
409+
-- date matches but no conflict
410+
INSERT INTO temporal3 (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICTONCONSTRAINT temporal3_uq DO NOTHING;
411+
SELECT*FROM temporal3ORDER BY id, valid_at;
412+
413+
TRUNCATE temporal3;
414+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
415+
-- with a conflict
416+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICT (id, valid_at) DOUPDATESET id=EXCLUDED.id+'[2,3)';
417+
-- id matches but no conflict
418+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICT (id, valid_at) DOUPDATESET id=EXCLUDED.id+'[3,4)';
419+
-- date matches but no conflict
420+
INSERT INTO temporal3 (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICT (id, valid_at) DOUPDATESET id=EXCLUDED.id+'[4,5)';
421+
SELECT*FROM temporal3ORDER BY id, valid_at;
422+
423+
TRUNCATE temporal3;
424+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2000-01-01','2010-01-01'));
425+
-- with a conflict
426+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2005-01-01','2006-01-01'))ON CONFLICTONCONSTRAINT temporal3_uq DOUPDATESET id=EXCLUDED.id+'[2,3)';
427+
-- id matches but no conflict
428+
INSERT INTO temporal3 (id, valid_at)VALUES ('[1,2)', daterange('2010-01-01','2020-01-01'))ON CONFLICTONCONSTRAINT temporal3_uq DOUPDATESET id=EXCLUDED.id+'[3,4)';
429+
-- date matches but no conflict
430+
INSERT INTO temporal3 (id, valid_at)VALUES ('[2,3)', daterange('2005-01-01','2006-01-01'))ON CONFLICTONCONSTRAINT temporal3_uq DOUPDATESET id=EXCLUDED.id+'[4,5)';
431+
SELECT*FROM temporal3ORDER BY id, valid_at;
432+
433+
DROPTABLE temporal3;
434+
322435
--
323436
-- test FK dependencies
324437
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp