From 94c9d23e5e3171da448544a3f5363965c80873ef Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Wed, 24 Jan 2024 15:43:41 +0100 Subject: [PATCH v36 2/3] 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. --- contrib/btree_gist/Makefile | 2 +- .../btree_gist/expected/without_overlaps.out | 44 + contrib/btree_gist/meson.build | 1 + contrib/btree_gist/sql/without_overlaps.sql | 25 + doc/src/sgml/catalogs.sgml | 10 + doc/src/sgml/gist.sgml | 14 +- doc/src/sgml/ref/create_table.sgml | 42 +- src/backend/access/gist/gistutil.c | 29 + src/backend/catalog/heap.c | 1 + src/backend/catalog/index.c | 4 + src/backend/catalog/pg_constraint.c | 2 + src/backend/commands/indexcmds.c | 143 ++- src/backend/commands/tablecmds.c | 6 +- src/backend/commands/trigger.c | 1 + src/backend/commands/typecmds.c | 2 + src/backend/executor/execIndexing.c | 2 +- src/backend/optimizer/util/plancat.c | 9 +- src/backend/parser/gram.y | 29 +- src/backend/parser/parse_utilcmd.c | 28 +- src/backend/utils/adt/ruleutils.c | 2 + src/backend/utils/cache/relcache.c | 18 +- src/bin/pg_dump/pg_dump.c | 16 +- src/bin/pg_dump/pg_dump.h | 1 + src/bin/pg_dump/t/002_pg_dump.pl | 36 + src/bin/psql/describe.c | 12 +- src/include/access/gist.h | 3 + src/include/catalog/index.h | 1 + src/include/catalog/pg_constraint.h | 10 +- src/include/commands/defrem.h | 6 +- src/include/nodes/parsenodes.h | 2 + .../regress/expected/without_overlaps.out | 1088 +++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/without_overlaps.sql | 754 ++++++++++++ 33 files changed, 2293 insertions(+), 52 deletions(-) create mode 100644 contrib/btree_gist/expected/without_overlaps.out create mode 100644 contrib/btree_gist/sql/without_overlaps.sql create mode 100644 src/test/regress/expected/without_overlaps.out create mode 100644 src/test/regress/sql/without_overlaps.sql diff --git a/contrib/btree_gist/Makefile b/contrib/btree_gist/Makefile index 9ab8548bc0e..7ac2df26c10 100644 --- a/contrib/btree_gist/Makefile +++ b/contrib/btree_gist/Makefile @@ -40,7 +40,7 @@ PGFILEDESC = "btree_gist - B-tree equivalent GiST operator classes" REGRESS = init int2 int4 int8 float4 float8 cash oid timestamp timestamptz \ time timetz date interval macaddr macaddr8 inet cidr text varchar char \ bytea bit varbit numeric uuid not_equal enum bool partitions \ - stratnum + stratnum without_overlaps SHLIB_LINK += $(filter -lm, $(LIBS)) diff --git a/contrib/btree_gist/expected/without_overlaps.out b/contrib/btree_gist/expected/without_overlaps.out new file mode 100644 index 00000000000..be52c522e89 --- /dev/null +++ b/contrib/btree_gist/expected/without_overlaps.out @@ -0,0 +1,44 @@ +-- 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 + Table "public.temporal_rng" + Column | Type | Collation | Nullable | Default +----------+-----------+-----------+----------+--------- + id | integer | | 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) + +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)'); +ERROR: conflicting key value violates exclusion constraint "temporal_rng_pk" +DETAIL: Key (id, valid_at)=(1, [06-01-2000,01-01-2001)) conflicts with existing key (id, valid_at)=(1, [01-01-2000,01-01-2001)). diff --git a/contrib/btree_gist/meson.build b/contrib/btree_gist/meson.build index a44ce905e59..73b1bbf52a6 100644 --- a/contrib/btree_gist/meson.build +++ b/contrib/btree_gist/meson.build @@ -91,6 +91,7 @@ tests += { 'bool', 'partitions', 'stratnum', + 'without_overlaps', ], }, } 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)'); diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index b654fae1b2f..1e56edaa70b 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2730,6 +2730,16 @@ SCRAM-SHA-256$<iteration count>:&l + + + conperiod bool + + + This constraint is defined with WITHOUT OVERLAPS + (for primary keys and unique constraints). + + + conkey int2[] diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index f789824c83b..dcf9433fa78 100644 --- a/doc/src/sgml/gist.sgml +++ b/doc/src/sgml/gist.sgml @@ -292,7 +292,8 @@ CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops); The optional twelfth method stratnum is used to translate well-known RT*StrategyNumbers (from src/include/access/stratnum.h) into strategy numbers - used by the operator class. + used by the operator class. This lets the core code look up operators for + temporal constraint indexes. @@ -1179,6 +1180,17 @@ my_sortsupport(PG_FUNCTION_ARGS) operator class has no matching strategy. + + This is used for temporal index constraints (i.e., PRIMARY + KEY and UNIQUE). If the operator class + provides this function and it returns results for + RTEqualStrategyNumber, it can be used in the + non-WITHOUT OVERLAPS part(s) of an index constraint. + If it returns results for RTOverlapStrategyNumber, + the operator class can be used in the WITHOUT + OVERLAPS part of an index constraint. + + The SQL declaration of the function must look like this: diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index f19306e7760..f2cda0c0e94 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -77,8 +77,8 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters | - PRIMARY KEY ( column_name [, ... ] ) index_parameters | + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | + PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE numeric_literal, REM UNIQUE [ NULLS [ NOT ] DISTINCT ] (column constraint) - UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) INCLUDE ( column_name [, ...]) (table constraint) @@ -978,6 +978,30 @@ WITH ( MODULUS numeric_literal, REM of these columns. + + If the WITHOUT OVERLAPS option is specified for the + last column, then that column is checked for overlaps instead of + equality. In that case, the other columns of the constraint will allow + duplicates so long as the duplicates don't overlap in the + WITHOUT OVERLAPS column. (This is sometimes called a + temporal key, if the column is a range of dates or timestamps, but + PostgreSQL allows ranges over any base type.) In effect, such a + constraint is enforced with an EXCLUDE constraint + rather than a UNIQUE constraint. So for example + UNIQUE (id, valid_at WITHOUT OVERLAPS) behaves like + EXCLUDE USING GIST (id WITH =, valid_at WITH + &&). The WITHOUT OVERLAPS column + must have a range or multirange type. (Technically, any type is allowed + whose default GiST opclass includes an overlaps operator. See the + stratnum support function under for details.) The non-WITHOUT + OVERLAPS columns of the constraint can be any type that can be + compared for equality in a GiST index. By default, only range types are + supported, but you can use other types by adding the extension (which is the expected way to use this + feature). + + For the purpose of a unique constraint, null values are not considered equal, unless NULLS NOT DISTINCT is @@ -1000,8 +1024,10 @@ WITH ( MODULUS numeric_literal, REM Adding a unique constraint will automatically create a unique btree - index on the column or group of columns used in the constraint. The - created index has the same name as the unique constraint. + index on the column or group of columns used in the constraint. But if + the constraint includes a WITHOUT OVERLAPS clause, it + will use a GiST index. The created index has the same name as the + unique constraint. @@ -1019,7 +1045,7 @@ WITH ( MODULUS numeric_literal, REM PRIMARY KEY (column constraint) - PRIMARY KEY ( column_name [, ... ] ) + PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) INCLUDE ( column_name [, ...]) (table constraint) @@ -1054,8 +1080,8 @@ WITH ( MODULUS numeric_literal, REM Adding a PRIMARY KEY constraint will automatically create a unique btree index on the column or group of columns used in - the constraint. That index has the same name as the primary key - constraint. + the constraint, or GiST if WITHOUT OVERLAPS was + specified. diff --git a/src/backend/access/gist/gistutil.c b/src/backend/access/gist/gistutil.c index 8686735f234..d2d0b36d4ea 100644 --- a/src/backend/access/gist/gistutil.c +++ b/src/backend/access/gist/gistutil.c @@ -1069,3 +1069,32 @@ gist_stratnum_identity(PG_FUNCTION_ARGS) PG_RETURN_UINT16(strat); } + +/* + * Returns the opclass's private stratnum used for the given strategy. + * + * Calls the opclass's GIST_STRATNUM_PROC support function, if any, + * and returns the result. + * Returns InvalidStrategy if the function is not defined. + */ +StrategyNumber +GistTranslateStratnum(Oid opclass, StrategyNumber strat) +{ + Oid opfamily; + Oid opcintype; + Oid funcid; + Datum result; + + /* Look up the opclass family and input datatype. */ + if (!get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) + return InvalidStrategy; + + /* Check whether the function is provided. */ + funcid = get_opfamily_proc(opfamily, opcintype, opcintype, GIST_STRATNUM_PROC); + if (!OidIsValid(funcid)) + return InvalidStrategy; + + /* Ask the translation function */ + result = OidFunctionCall1Coll(funcid, InvalidOid, UInt16GetDatum(strat)); + return DatumGetUInt16(result); +} diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 00074c8a948..8ea7768e244 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -2163,6 +2163,7 @@ StoreRelCheck(Relation rel, const char *ccname, Node *expr, is_local, /* conislocal */ inhcount, /* coninhcount */ is_no_inherit, /* connoinherit */ + false, /* conperiod */ is_internal); /* internally constructed? */ pfree(ccbin); diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index a819b4197ce..5c48e5728e9 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1874,6 +1874,7 @@ index_concurrently_set_dead(Oid heapId, Oid indexId) * INDEX_CONSTR_CREATE_UPDATE_INDEX: update the pg_index row * INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS: remove existing dependencies * of index on table's columns + * INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS: constraint uses WITHOUT OVERLAPS * allow_system_table_mods: allow table to be a system catalog * is_internal: index is constructed due to internal process */ @@ -1897,11 +1898,13 @@ index_constraint_create(Relation heapRelation, bool mark_as_primary; bool islocal; bool noinherit; + bool is_without_overlaps; int inhcount; deferrable = (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) != 0; initdeferred = (constr_flags & INDEX_CONSTR_CREATE_INIT_DEFERRED) != 0; mark_as_primary = (constr_flags & INDEX_CONSTR_CREATE_MARK_AS_PRIMARY) != 0; + is_without_overlaps = (constr_flags & INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS) != 0; /* constraint creation support doesn't work while bootstrapping */ Assert(!IsBootstrapProcessingMode()); @@ -1978,6 +1981,7 @@ index_constraint_create(Relation heapRelation, islocal, inhcount, noinherit, + is_without_overlaps, is_internal); /* diff --git a/src/backend/catalog/pg_constraint.c b/src/backend/catalog/pg_constraint.c index 3baf9231ed0..9be050ccee8 100644 --- a/src/backend/catalog/pg_constraint.c +++ b/src/backend/catalog/pg_constraint.c @@ -75,6 +75,7 @@ CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, + bool conPeriod, bool is_internal) { Relation conDesc; @@ -190,6 +191,7 @@ CreateConstraintEntry(const char *constraintName, values[Anum_pg_constraint_conislocal - 1] = BoolGetDatum(conIsLocal); values[Anum_pg_constraint_coninhcount - 1] = Int16GetDatum(conInhCount); values[Anum_pg_constraint_connoinherit - 1] = BoolGetDatum(conNoInherit); + values[Anum_pg_constraint_conperiod - 1] = BoolGetDatum(conPeriod); if (conkeyArray) values[Anum_pg_constraint_conkey - 1] = PointerGetDatum(conkeyArray); diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index c5a56c75f69..47e139b3828 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -16,6 +16,7 @@ #include "postgres.h" #include "access/amapi.h" +#include "access/gist.h" #include "access/heapam.h" #include "access/htup_details.h" #include "access/reloptions.h" @@ -87,6 +88,7 @@ static void ComputeIndexAttrs(IndexInfo *indexInfo, Oid accessMethodId, bool amcanorder, bool isconstraint, + bool iswithoutoverlaps, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel); @@ -145,6 +147,7 @@ typedef struct ReindexErrorInfo * to index on. * 'exclusionOpNames': list of names of exclusion-constraint operators, * or NIL if not an exclusion constraint. + * 'isWithoutOverlaps': true iff this index has a WITHOUT OVERLAPS clause. * * This is tailored to the needs of ALTER TABLE ALTER TYPE, which recreates * any indexes that depended on a changing column from their pg_get_indexdef @@ -174,7 +177,8 @@ bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, const List *attributeList, - const List *exclusionOpNames) + const List *exclusionOpNames, + bool isWithoutOverlaps) { bool isconstraint; Oid *typeIds; @@ -249,8 +253,8 @@ CheckIndexCompatible(Oid oldId, coloptions, attributeList, exclusionOpNames, relationId, accessMethodName, accessMethodId, - amcanorder, isconstraint, InvalidOid, 0, NULL); - + amcanorder, isconstraint, isWithoutOverlaps, InvalidOid, + 0, NULL); /* Get the soon-obsolete pg_index tuple. */ tuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(oldId)); @@ -560,6 +564,7 @@ DefineIndex(Oid tableId, bool amcanorder; bool amissummarizing; amoptions_function amoptions; + bool exclusion; bool partitioned; bool safe_index; Datum reloptions; @@ -680,6 +685,12 @@ DefineIndex(Oid tableId, namespaceId = RelationGetNamespace(rel); + /* + * It has exclusion constraint behavior if it's an EXCLUDE constraint or a + * temporal PRIMARY KEY/UNIQUE constraint + */ + exclusion = stmt->excludeOpNames || stmt->iswithoutoverlaps; + /* Ensure that it makes sense to index this kind of relation */ switch (rel->rd_rel->relkind) { @@ -848,7 +859,7 @@ DefineIndex(Oid tableId, pgstat_progress_update_param(PROGRESS_CREATEIDX_ACCESS_METHOD_OID, accessMethodId); - if (stmt->unique && !amRoutine->amcanunique) + if (stmt->unique && !stmt->iswithoutoverlaps && !amRoutine->amcanunique) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("access method \"%s\" does not support unique indexes", @@ -863,7 +874,7 @@ DefineIndex(Oid tableId, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("access method \"%s\" does not support multicolumn indexes", accessMethodName))); - if (stmt->excludeOpNames && amRoutine->amgettuple == NULL) + if (exclusion && amRoutine->amgettuple == NULL) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("access method \"%s\" does not support exclusion constraints", @@ -916,8 +927,9 @@ DefineIndex(Oid tableId, coloptions, allIndexParams, stmt->excludeOpNames, tableId, accessMethodName, accessMethodId, - amcanorder, stmt->isconstraint, root_save_userid, - root_save_sec_context, &root_save_nestlevel); + amcanorder, stmt->isconstraint, stmt->iswithoutoverlaps, + root_save_userid, root_save_sec_context, + &root_save_nestlevel); /* * Extra checks when creating a PRIMARY KEY index. @@ -935,7 +947,7 @@ DefineIndex(Oid tableId, * We could lift this limitation if we had global indexes, but those have * their own problems, so this is a useful feature combination. */ - if (partitioned && (stmt->unique || stmt->excludeOpNames)) + if (partitioned && (stmt->unique || exclusion)) { PartitionKey key = RelationGetPartitionKey(rel); const char *constraint_type; @@ -989,10 +1001,10 @@ DefineIndex(Oid tableId, * associated with index columns, too. We know what to do with * btree opclasses; if there are ever any other index types that * support unique indexes, this logic will need extension. But if - * we have an exclusion constraint, it already knows the - * operators, so we don't have to infer them. + * we have an exclusion constraint (or a temporal PK), it already + * knows the operators, so we don't have to infer them. */ - if (stmt->unique && accessMethodId != BTREE_AM_OID) + if (stmt->unique && !stmt->iswithoutoverlaps && accessMethodId != BTREE_AM_OID) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot match partition key to an index using access method \"%s\"", @@ -1031,12 +1043,12 @@ DefineIndex(Oid tableId, { Oid idx_eqop = InvalidOid; - if (stmt->unique) + if (stmt->unique && !stmt->iswithoutoverlaps) idx_eqop = get_opfamily_member(idx_opfamily, idx_opcintype, idx_opcintype, BTEqualStrategyNumber); - else if (stmt->excludeOpNames) + else if (exclusion) idx_eqop = indexInfo->ii_ExclusionOps[j]; Assert(idx_eqop); @@ -1045,7 +1057,7 @@ DefineIndex(Oid tableId, found = true; break; } - else if (stmt->excludeOpNames) + else if (exclusion) { /* * We found a match, but it's not an equality @@ -1189,6 +1201,8 @@ DefineIndex(Oid tableId, constr_flags |= INDEX_CONSTR_CREATE_DEFERRABLE; if (stmt->initdeferred) constr_flags |= INDEX_CONSTR_CREATE_INIT_DEFERRED; + if (stmt->iswithoutoverlaps) + constr_flags |= INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS; indexRelationId = index_create(rel, indexRelationName, indexRelationId, parentIndexId, @@ -1855,6 +1869,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo, Oid accessMethodId, bool amcanorder, bool isconstraint, + bool iswithoutoverlaps, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel) @@ -1878,6 +1893,14 @@ ComputeIndexAttrs(IndexInfo *indexInfo, else nextExclOp = NULL; + /* exclusionOpNames can be non-NIL if we are creating a partition */ + if (iswithoutoverlaps && exclusionOpNames == NIL) + { + indexInfo->ii_ExclusionOps = palloc_array(Oid, nkeycols); + indexInfo->ii_ExclusionProcs = palloc_array(Oid, nkeycols); + indexInfo->ii_ExclusionStrats = palloc_array(uint16, nkeycols); + } + if (OidIsValid(ddl_userid)) GetUserIdAndSecContext(&save_userid, &save_sec_context); @@ -2157,6 +2180,21 @@ ComputeIndexAttrs(IndexInfo *indexInfo, indexInfo->ii_ExclusionStrats[attn] = strat; nextExclOp = lnext(exclusionOpNames, nextExclOp); } + else if (iswithoutoverlaps) + { + StrategyNumber strat; + Oid opid; + + if (attn == nkeycols - 1) + strat = RTOverlapStrategyNumber; + else + strat = RTEqualStrategyNumber; + GetOperatorFromWellKnownStrategy(opclassOids[attn], atttype, + &opid, &strat); + indexInfo->ii_ExclusionOps[attn] = opid; + indexInfo->ii_ExclusionProcs[attn] = get_opcode(opid); + indexInfo->ii_ExclusionStrats[attn] = strat; + } /* * Set up the per-column options (indoption field). For now, this is @@ -2387,6 +2425,83 @@ GetDefaultOpClass(Oid type_id, Oid am_id) return InvalidOid; } +/* + * GetOperatorFromWellKnownStrategy + * + * opclass - the opclass to use + * atttype - the type to ask about + * opid - holds the operator we found + * strat - holds the input and output strategy number + * + * Finds an operator from a "well-known" strategy number. This is used for + * temporal index constraints (and other temporal features) to look up + * equality and overlaps operators, since the strategy numbers for non-btree + * indexams need not follow any fixed scheme. We ask an opclass support + * function to translate from the well-known number to the internal value. If + * the function isn't defined or it gives no result, we return + * InvalidStrategy. + */ +void +GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, + Oid *opid, StrategyNumber *strat) +{ + Oid opfamily; + Oid opcintype; + StrategyNumber instrat = *strat; + + Assert(instrat == RTEqualStrategyNumber || instrat == RTOverlapStrategyNumber); + + *opid = InvalidOid; + + if (get_opclass_opfamily_and_input_type(opclass, &opfamily, &opcintype)) + { + /* + * Ask the opclass to translate to its internal stratnum + * + * For now we only need GiST support, but this could support other + * indexams if we wanted. + */ + *strat = GistTranslateStratnum(opclass, instrat); + if (*strat == InvalidStrategy) + { + HeapTuple tuple; + + tuple = SearchSysCache1(CLAOID, ObjectIdGetDatum(opclass)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for operator class %u", opclass); + + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + instrat == RTEqualStrategyNumber ? + errmsg("could not identify an equality operator for type %s", format_type_be(atttype)) : + errmsg("could not identify an overlaps operator for type %s", format_type_be(atttype)), + errdetail("Could not translate strategy number %d for operator class \"%s\" for access method \"%s\".", + instrat, NameStr(((Form_pg_opclass) GETSTRUCT(tuple))->opcname), "gist")); + + ReleaseSysCache(tuple); + } + + *opid = get_opfamily_member(opfamily, opcintype, opcintype, *strat); + } + + if (!OidIsValid(*opid)) + { + HeapTuple tuple; + + tuple = SearchSysCache1(OPFAMILYOID, ObjectIdGetDatum(opfamily)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for operator family %u", opfamily); + + ereport(ERROR, + errcode(ERRCODE_UNDEFINED_OBJECT), + instrat == RTEqualStrategyNumber ? + errmsg("could not identify an equality operator for type %s", format_type_be(atttype)) : + errmsg("could not identify an overlaps operator for type %s", format_type_be(atttype)), + errdetail("There is no suitable operator in operator family \"%s\" for access method \"%s\".", + NameStr(((Form_pg_opfamily) GETSTRUCT(tuple))->opfname), "gist")); + } +} + /* * makeObjectName() * diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 721d24783b4..c39e8bc161d 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10152,6 +10152,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ + false, /* conPeriod */ false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -10450,6 +10451,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, + false, /* conPeriod */ false); /* @@ -10955,6 +10957,7 @@ CloneFkReferencing(List **wqueue, Relation parentRel, Relation partRel) false, /* islocal */ 1, /* inhcount */ false, /* conNoInherit */ + false, /* conPeriod */ true); /* Set up partition dependencies for the new constraint */ @@ -14124,7 +14127,8 @@ TryReuseIndex(Oid oldId, IndexStmt *stmt) if (CheckIndexCompatible(oldId, stmt->accessMethod, stmt->indexParams, - stmt->excludeOpNames)) + stmt->excludeOpNames, + stmt->iswithoutoverlaps)) { Relation irel = index_open(oldId, NoLock); diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index 170360edda8..29d30bfb6f7 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -834,6 +834,7 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString, true, /* islocal */ 0, /* inhcount */ true, /* noinherit */ + false, /* conperiod */ isInternal); /* is_internal */ } diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 2a1e7133356..2a6550de907 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -3621,6 +3621,7 @@ domainAddCheckConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, true, /* is local */ 0, /* inhcount */ false, /* connoinherit */ + false, /* conperiod */ false); /* is_internal */ if (constrAddr) ObjectAddressSet(*constrAddr, ConstraintRelationId, ccoid); @@ -3727,6 +3728,7 @@ domainAddNotNullConstraint(Oid domainOid, Oid domainNamespace, Oid baseTypeOid, true, /* is local */ 0, /* inhcount */ false, /* connoinherit */ + false, /* conperiod */ false); /* is_internal */ if (constrAddr) diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c index 9f05b3654c1..59acf67a36a 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) + if (speculative && ii->ii_Unique && !indexDesc->rd_index->indisexclusion) BuildSpeculativeIndexInfo(indexDesc, ii); relationDescs[i] = indexDesc; diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 9efdd844aac..eee69976ec9 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -817,7 +817,7 @@ infer_arbiter_indexes(PlannerInfo *root) */ if (indexOidFromConstraint == idxForm->indexrelid) { - if (!idxForm->indisunique && onconflict->action == ONCONFLICT_UPDATE) + if (idxForm->indisexclusion && onconflict->action == ONCONFLICT_UPDATE) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("ON CONFLICT DO UPDATE not supported with exclusion constraints"))); @@ -842,6 +842,13 @@ 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/backend/parser/gram.y b/src/backend/parser/gram.y index a043fd4c669..eafa290b88b 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -532,7 +532,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); columnref in_expr having_clause func_table xmltable array_expr OptWhereClause operator_def_arg %type rowsfrom_item rowsfrom_list opt_col_def_list -%type opt_ordinality +%type opt_ordinality opt_without_overlaps %type ExclusionConstraintList ExclusionConstraintElem %type func_arg_list func_arg_list_opt %type func_arg_expr @@ -4193,7 +4193,7 @@ ConstraintElem: n->initially_valid = !n->skip_validation; $$ = (Node *) n; } - | UNIQUE opt_unique_null_treatment '(' columnList ')' opt_c_include opt_definition OptConsTableSpace + | UNIQUE opt_unique_null_treatment '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); @@ -4202,11 +4202,12 @@ ConstraintElem: n->location = @1; n->nulls_not_distinct = !$2; n->keys = $4; - n->including = $6; - n->options = $7; + n->without_overlaps = $5; + n->including = $7; + n->options = $8; n->indexname = NULL; - n->indexspace = $8; - processCASbits($9, @9, "UNIQUE", + n->indexspace = $9; + processCASbits($10, @10, "UNIQUE", &n->deferrable, &n->initdeferred, NULL, NULL, yyscanner); $$ = (Node *) n; @@ -4227,7 +4228,7 @@ ConstraintElem: NULL, yyscanner); $$ = (Node *) n; } - | PRIMARY KEY '(' columnList ')' opt_c_include opt_definition OptConsTableSpace + | PRIMARY KEY '(' columnList opt_without_overlaps ')' opt_c_include opt_definition OptConsTableSpace ConstraintAttributeSpec { Constraint *n = makeNode(Constraint); @@ -4235,11 +4236,12 @@ ConstraintElem: n->contype = CONSTR_PRIMARY; n->location = @1; n->keys = $4; - n->including = $6; - n->options = $7; + n->without_overlaps = $5; + n->including = $7; + n->options = $8; n->indexname = NULL; - n->indexspace = $8; - processCASbits($9, @9, "PRIMARY KEY", + n->indexspace = $9; + processCASbits($10, @10, "PRIMARY KEY", &n->deferrable, &n->initdeferred, NULL, NULL, yyscanner); $$ = (Node *) n; @@ -4361,6 +4363,11 @@ opt_no_inherit: NO INHERIT { $$ = true; } | /* EMPTY */ { $$ = false; } ; +opt_without_overlaps: + WITHOUT OVERLAPS { $$ = true; } + | /*EMPTY*/ { $$ = false; } + ; + opt_column_list: '(' columnList ')' { $$ = $2; } | /*EMPTY*/ { $$ = NIL; } diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index d5c2b2ff0b0..53492c644a9 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1558,6 +1558,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, index->unique = idxrec->indisunique; index->nulls_not_distinct = idxrec->indnullsnotdistinct; index->primary = idxrec->indisprimary; + index->iswithoutoverlaps = (idxrec->indisprimary || idxrec->indisunique) && idxrec->indisexclusion; index->transformed = true; /* don't need transformIndexStmt */ index->concurrent = false; index->if_not_exists = false; @@ -1607,7 +1608,9 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, int nElems; int i; - Assert(conrec->contype == CONSTRAINT_EXCLUSION); + Assert(conrec->contype == CONSTRAINT_EXCLUSION || + (index->iswithoutoverlaps && + (conrec->contype == CONSTRAINT_PRIMARY || conrec->contype == CONSTRAINT_UNIQUE))); /* Extract operator OIDs from the pg_constraint tuple */ datum = SysCacheGetAttrNotNull(CONSTROID, ht_constr, Anum_pg_constraint_conexclop); @@ -2160,6 +2163,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) } index->nulls_not_distinct = constraint->nulls_not_distinct; index->isconstraint = true; + index->iswithoutoverlaps = constraint->without_overlaps; index->deferrable = constraint->deferrable; index->initdeferred = constraint->initdeferred; @@ -2252,6 +2256,11 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) errmsg("index \"%s\" is not valid", index_name), parser_errposition(cxt->pstate, constraint->location))); + /* + * Today we forbid non-unique indexes, but we could permit GiST + * indexes whose last entry is a range type and use that to create a + * WITHOUT OVERLAPS constraint (i.e. a temporal constraint). + */ if (!index_form->indisunique) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -2540,6 +2549,23 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) notnullcmds = lappend(notnullcmds, notnullcmd); } } + + if (constraint->without_overlaps) + { + /* + * This enforces that there is at least one equality column + * besides the WITHOUT OVERLAPS columns. This is per SQL + * standard. XXX Do we need this? + */ + if (list_length(constraint->keys) < 2) + ereport(ERROR, + errcode(ERRCODE_SYNTAX_ERROR), + errmsg("constraint using WITHOUT OVERLAPS needs at least two columns")); + + /* WITHOUT OVERLAPS requires a GiST index */ + index->accessMethod = "gist"; + } + } /* diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 653685bffc5..50c0247c300 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2393,6 +2393,8 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand, Anum_pg_constraint_conkey); keyatts = decompile_column_index_array(val, conForm->conrelid, &buf); + if (conForm->conperiod) + appendStringInfoString(&buf, " WITHOUT OVERLAPS"); appendStringInfoChar(&buf, ')'); diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 66ed24e4012..5e7a1499e1e 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -5581,11 +5581,14 @@ RelationGetIdentityKeyBitmap(Relation relation) /* * RelationGetExclusionInfo -- get info about index's exclusion constraint * - * This should be called only for an index that is known to have an - * associated exclusion constraint. It returns arrays (palloc'd in caller's - * context) of the exclusion operator OIDs, their underlying functions' - * OIDs, and their strategy numbers in the index's opclasses. We cache - * all this information since it requires a fair amount of work to get. + * This should be called only for an index that is known to have an associated + * exclusion constraint or primary key/unique constraint using WITHOUT + * OVERLAPS. + + * It returns arrays (palloc'd in caller's context) of the exclusion operator + * OIDs, their underlying functions' OIDs, and their strategy numbers in the + * index's opclasses. We cache all this information since it requires a fair + * amount of work to get. */ void RelationGetExclusionInfo(Relation indexRelation, @@ -5649,7 +5652,10 @@ RelationGetExclusionInfo(Relation indexRelation, int nelem; /* We want the exclusion constraint owning the index */ - if (conform->contype != CONSTRAINT_EXCLUSION || + if ((conform->contype != CONSTRAINT_EXCLUSION && + !(conform->conperiod && ( + conform->contype == CONSTRAINT_PRIMARY + || conform->contype == CONSTRAINT_UNIQUE))) || conform->conindid != RelationGetRelid(indexRelation)) continue; diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index b8b1888bd33..67127dbd9fe 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -7326,6 +7326,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_conname, i_condeferrable, i_condeferred, + i_conperiod, i_contableoid, i_conoid, i_condef, @@ -7407,10 +7408,17 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) if (fout->remoteVersion >= 150000) appendPQExpBufferStr(query, - "i.indnullsnotdistinct "); + "i.indnullsnotdistinct, "); else appendPQExpBufferStr(query, - "false AS indnullsnotdistinct "); + "false AS indnullsnotdistinct, "); + + if (fout->remoteVersion >= 170000) + appendPQExpBufferStr(query, + "c.conperiod "); + else + appendPQExpBufferStr(query, + "NULL AS conperiod "); /* * The point of the messy-looking outer join is to find a constraint that @@ -7478,6 +7486,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_conname = PQfnumber(res, "conname"); i_condeferrable = PQfnumber(res, "condeferrable"); i_condeferred = PQfnumber(res, "condeferred"); + i_conperiod = PQfnumber(res, "conperiod"); i_contableoid = PQfnumber(res, "contableoid"); i_conoid = PQfnumber(res, "conoid"); i_condef = PQfnumber(res, "condef"); @@ -7585,6 +7594,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) constrinfo->conindex = indxinfo[j].dobj.dumpId; constrinfo->condeferrable = *(PQgetvalue(res, j, i_condeferrable)) == 't'; constrinfo->condeferred = *(PQgetvalue(res, j, i_condeferred)) == 't'; + constrinfo->conperiod = *(PQgetvalue(res, j, i_conperiod)) == 't'; constrinfo->conislocal = true; constrinfo->separate = true; @@ -16954,6 +16964,8 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) (k == 0) ? "" : ", ", fmtId(attname)); } + if (coninfo->conperiod) + appendPQExpBufferStr(q, " WITHOUT OVERLAPS"); if (indxinfo->indnkeyattrs < indxinfo->indnattrs) appendPQExpBufferStr(q, ") INCLUDE ("); diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 4b2e5870a9c..b25b9f9304e 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -474,6 +474,7 @@ typedef struct _constraintInfo DumpId conindex; /* identifies associated index if any */ bool condeferrable; /* true if constraint is DEFERRABLE */ bool condeferred; /* true if constraint is INITIALLY DEFERRED */ + bool conperiod; /* true if the constraint is WITHOUT OVERLAPS */ bool conislocal; /* true if constraint has local definition */ bool separate; /* true if must dump as separate item */ } ConstraintInfo; diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index d3dd8784d64..beed8db4d10 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -1004,6 +1004,42 @@ my %tests = ( }, }, + 'CONSTRAINT PRIMARY KEY / WITHOUT OVERLAPS' => { + create_sql => 'CREATE TABLE dump_test.test_table_tpk ( + col1 int4range, + col2 tstzrange, + CONSTRAINT test_table_tpk_pkey PRIMARY KEY (col1, col2 WITHOUT OVERLAPS));', + regexp => qr/^ + \QALTER TABLE ONLY dump_test.test_table_tpk\E \n^\s+ + \QADD CONSTRAINT test_table_tpk_pkey PRIMARY KEY (col1, col2 WITHOUT OVERLAPS);\E + /xm, + like => { + %full_runs, %dump_test_schema_runs, section_post_data => 1, + }, + unlike => { + exclude_dump_test_schema => 1, + only_dump_measurement => 1, + }, + }, + + 'CONSTRAINT UNIQUE / WITHOUT OVERLAPS' => { + create_sql => 'CREATE TABLE dump_test.test_table_tuq ( + col1 int4range, + col2 tstzrange, + CONSTRAINT test_table_tuq_uq UNIQUE (col1, col2 WITHOUT OVERLAPS));', + regexp => qr/^ + \QALTER TABLE ONLY dump_test.test_table_tuq\E \n^\s+ + \QADD CONSTRAINT test_table_tuq_uq UNIQUE (col1, col2 WITHOUT OVERLAPS);\E + /xm, + like => { + %full_runs, %dump_test_schema_runs, section_post_data => 1, + }, + unlike => { + exclude_dump_test_schema => 1, + only_dump_measurement => 1, + }, + }, + 'ALTER TABLE (partitioned) ADD CONSTRAINT ... FOREIGN KEY' => { create_order => 4, create_sql => 'CREATE TABLE dump_test.test_table_fk ( diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 7c9a1f234c6..90b2edb552f 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2383,6 +2383,10 @@ describeOneTableDetails(const char *schemaname, else appendPQExpBufferStr(&buf, ", false AS indisreplident"); appendPQExpBufferStr(&buf, ", c2.reltablespace"); + if (pset.sversion >= 170000) + appendPQExpBufferStr(&buf, ", con.conperiod"); + else + appendPQExpBufferStr(&buf, ", false AS conperiod"); appendPQExpBuffer(&buf, "\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n" " LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))\n" @@ -2404,8 +2408,12 @@ describeOneTableDetails(const char *schemaname, printfPQExpBuffer(&buf, " \"%s\"", PQgetvalue(result, i, 0)); - /* If exclusion constraint, print the constraintdef */ - if (strcmp(PQgetvalue(result, i, 7), "x") == 0) + /* + * If exclusion constraint or PK/UNIQUE constraint WITHOUT + * OVERLAPS, print the constraintdef + */ + if (strcmp(PQgetvalue(result, i, 7), "x") == 0 || + strcmp(PQgetvalue(result, i, 12), "t") == 0) { appendPQExpBuffer(&buf, " %s", PQgetvalue(result, i, 6)); diff --git a/src/include/access/gist.h b/src/include/access/gist.h index e7ced18a5ba..22dd04c1418 100644 --- a/src/include/access/gist.h +++ b/src/include/access/gist.h @@ -17,6 +17,7 @@ #define GIST_H #include "access/itup.h" +#include "access/stratnum.h" #include "access/transam.h" #include "access/xlog.h" #include "access/xlogdefs.h" @@ -246,4 +247,6 @@ typedef struct do { (e).key = (k); (e).rel = (r); (e).page = (pg); \ (e).offset = (o); (e).leafkey = (l); } while (0) +extern StrategyNumber GistTranslateStratnum(Oid opclass, StrategyNumber strat); + #endif /* GIST_H */ diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 7d434f8e653..2dea96f47c3 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -93,6 +93,7 @@ extern Oid index_create(Relation heapRelation, #define INDEX_CONSTR_CREATE_INIT_DEFERRED (1 << 2) #define INDEX_CONSTR_CREATE_UPDATE_INDEX (1 << 3) #define INDEX_CONSTR_CREATE_REMOVE_OLD_DEPS (1 << 4) +#define INDEX_CONSTR_CREATE_WITHOUT_OVERLAPS (1 << 5) extern Oid index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, diff --git a/src/include/catalog/pg_constraint.h b/src/include/catalog/pg_constraint.h index 7a8017f15b5..96c00624b15 100644 --- a/src/include/catalog/pg_constraint.h +++ b/src/include/catalog/pg_constraint.h @@ -107,6 +107,12 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) /* Has a local definition and cannot be inherited */ bool connoinherit; + /* + * For primary keys and unique constraints, signifies the last column uses + * overlaps instead of equals. + */ + bool conperiod; + #ifdef CATALOG_VARLEN /* variable-length fields start here */ /* @@ -146,7 +152,8 @@ CATALOG(pg_constraint,2606,ConstraintRelationId) /* * If an exclusion constraint, the OIDs of the exclusion operators for - * each column of the constraint + * each column of the constraint. Also set for unique constraints/primary + * keys using WITHOUT OVERLAPS. */ Oid conexclop[1] BKI_LOOKUP(pg_operator); @@ -238,6 +245,7 @@ extern Oid CreateConstraintEntry(const char *constraintName, bool conIsLocal, int conInhCount, bool conNoInherit, + bool conPeriod, bool is_internal); extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId, diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 29c511e3196..0c53d67d3ee 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -14,6 +14,7 @@ #ifndef DEFREM_H #define DEFREM_H +#include "access/stratnum.h" #include "catalog/objectaddress.h" #include "nodes/params.h" #include "parser/parse_node.h" @@ -44,10 +45,13 @@ extern char *ChooseRelationName(const char *name1, const char *name2, extern bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, const List *attributeList, - const List *exclusionOpNames); + const List *exclusionOpNames, + bool isWithoutOverlaps); extern Oid GetDefaultOpClass(Oid type_id, Oid am_id); extern Oid ResolveOpClass(const List *opclass, Oid attrType, const char *accessMethodName, Oid accessMethodId); +extern void GetOperatorFromWellKnownStrategy(Oid opclass, Oid atttype, + Oid *opid, StrategyNumber *strat); /* commands/functioncmds.c */ extern ObjectAddress CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 85a62b538e5..9dc4667d2bd 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2750,6 +2750,7 @@ typedef struct Constraint bool nulls_not_distinct; /* null treatment for UNIQUE constraints */ List *keys; /* String nodes naming referenced key * column(s); for UNIQUE/PK/NOT NULL */ + bool without_overlaps; /* WITHOUT OVERLAPS specified */ List *including; /* String nodes naming referenced nonkey * column(s); for UNIQUE/PK */ List *exclusions; /* list of (IndexElem, operator name) pairs; @@ -3373,6 +3374,7 @@ typedef struct IndexStmt bool nulls_not_distinct; /* null treatment for UNIQUE constraints */ bool primary; /* is index a primary key? */ bool isconstraint; /* is it for a pkey/unique constraint? */ + bool iswithoutoverlaps; /* is the constraint WITHOUT OVERLAPS? */ bool deferrable; /* is the constraint DEFERRABLE? */ bool initdeferred; /* is the constraint INITIALLY DEFERRED? */ bool transformed; /* true when transformIndexStmt is finished */ diff --git a/src/test/regress/expected/without_overlaps.out b/src/test/regress/expected/without_overlaps.out new file mode 100644 index 00000000000..94234758598 --- /dev/null +++ b/src/test/regress/expected/without_overlaps.out @@ -0,0 +1,1088 @@ +-- Tests for WITHOUT OVERLAPS. +-- +-- We leave behind several tables to test pg_dump etc: +-- temporal_rng, temporal_rng2, +-- temporal_fk_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 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 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 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 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 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; +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). +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). +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; +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)). +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)). +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; +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). +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). +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; +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)}). +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)}). +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') +; +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_paritioned_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 * FROM temporal_partitioned ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2000-01-01,2000-02-01) | one + [1,2) | [2000-02-01,2000-03-01) | one + [3,4) | [2000-01-01,2010-01-01) | three +(3 rows) + +SELECT * FROM tp1 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2000-01-01,2000-02-01) | one + [1,2) | [2000-02-01,2000-03-01) | one +(2 rows) + +SELECT * FROM tp2 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [3,4) | [2000-01-01,2010-01-01) | 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,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 * FROM temporal_partitioned ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [1,2) | [2000-01-01,2000-02-01) | one + [1,2) | [2000-02-01,2000-03-01) | one + [3,4) | [2000-01-01,2010-01-01) | three +(3 rows) + +SELECT * FROM tp1 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------ + [1,2) | [2000-01-01,2000-02-01) | one + [1,2) | [2000-02-01,2000-03-01) | one +(2 rows) + +SELECT * FROM tp2 ORDER BY id, valid_at; + id | valid_at | name +-------+-------------------------+------- + [3,4) | [2000-01-01,2010-01-01) | three +(1 row) + +DROP TABLE temporal_partitioned; +-- ALTER TABLE REPLICA IDENTITY +-- (should fail) +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; +RESET datestyle; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 2429ec2bbaa..ddc155c195f 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.utf8 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.utf8 collate.icu.utf8 incremental_sort create_role without_overlaps # collate.linux.utf8 and collate.icu.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..36b3e6dc02a --- /dev/null +++ b/src/test/regress/sql/without_overlaps.sql @@ -0,0 +1,754 @@ +-- Tests for WITHOUT OVERLAPS. +-- +-- We leave behind several tables to test pg_dump etc: +-- temporal_rng, temporal_rng2, +-- temporal_fk_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) +); + +-- 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 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'; + +-- PK from LIKE: +CREATE TABLE temporal_rng2 (LIKE temporal_rng INCLUDING ALL); +\d temporal_rng2 +DROP TABLE temporal_rng2; + +-- no PK from INHERITS: +CREATE TABLE temporal_rng2 () INHERITS (temporal_rng); +\d temporal_rng2 +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 +DROP TABLE temporal_rng CASCADE; + +-- 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 +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 +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 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 +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng_pk'; +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng_pk'; + +-- 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 +SELECT pg_get_constraintdef(oid) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; +SELECT pg_get_indexdef(conindid, 0, true) FROM pg_constraint WHERE conname = 'temporal_mltrng2_pk'; + +-- UNIQUE with no columns just WITHOUT OVERLAPS: + +CREATE TABLE temporal_rng3 ( + valid_at daterange, + 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 daterange, + 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 daterange, + 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 +-- + +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; +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; +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; +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); +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')); +INSERT INTO temporal_rng (id, valid_at) VALUES (NULL, daterange('2018-01-01', '2018-01-05')); +INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', NULL); +SELECT * FROM temporal_rng ORDER BY id, valid_at; + +-- +-- 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; +-- should fail: +UPDATE temporal_rng +SET id = '[1,2)', + valid_at = daterange('2018-03-05', '2018-05-05') +WHERE id = '[21,22)'; +-- 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)'; +-- set the range part to NULL +UPDATE temporal_rng +SET id = '[1,2)', + valid_at = NULL +WHERE id = '[21,22)'; +SELECT * FROM temporal_rng ORDER BY id, valid_at; + +-- +-- 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); +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')); +SELECT * FROM temporal_rng3 ORDER BY id, valid_at; + +-- +-- 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; +-- should fail: +UPDATE temporal_rng3 +SET valid_at = daterange('2018-03-01', '2018-05-05') +WHERE id = '[1,2)' AND valid_at IS NULL; +SELECT * FROM temporal_rng3 ORDER BY id, valid_at; +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); +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'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES (NULL, datemultirange(daterange('2018-01-01', '2018-01-05'))); +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', NULL); +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + +-- +-- 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; +-- should fail: +UPDATE temporal_mltrng +SET id = '[1,2)', + valid_at = datemultirange(daterange('2018-03-05', '2018-05-05')) +WHERE id = '[21,22)'; +-- 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)'; +-- set the multirange part to NULL +UPDATE temporal_mltrng +SET id = '[1,2)', + valid_at = NULL +WHERE id = '[21,22)'; +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + +-- +-- 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); +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'))); +SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; + +-- +-- 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; +-- 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; +SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; +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') +; +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; +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,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 * 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,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 * 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; + +-- ALTER TABLE REPLICA IDENTITY +-- (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; + +RESET datestyle; -- 2.42.0