diff options
37 files changed, 2848 insertions, 62 deletions
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 @@ -2732,6 +2732,16 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l <row> <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>conperiod</structfield> <type>bool</type> + </para> + <para> + This constraint is defined with <literal>WITHOUT OVERLAPS</literal> + (for primary keys and unique constraints). + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> <structfield>conkey</structfield> <type>int2[]</type> (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>) </para> diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index f789824c83b..638d912dc2d 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 <function>stratnum</function> is used to translate well-known <literal>RT*StrategyNumber</literal>s (from <filename>src/include/access/stratnum.h</filename>) 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. </para> <variablelist> @@ -1180,6 +1181,14 @@ my_sortsupport(PG_FUNCTION_ARGS) </para> <para> + This is used for temporal index constraints (i.e., <literal>PRIMARY + KEY</literal> and <literal>UNIQUE</literal>). If the operator class + provides this function and it returns results for + <literal>RTEqualStrategyNumber</literal>, it can be used in the + non-<literal>WITHOUT OVERLAPS</literal> part(s) of an index constraint. + </para> + + <para> The <acronym>SQL</acronym> 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 93b3f664f21..9243810c3fe 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 <replaceable class="parameter">constraint_name</replaceable> ] { CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] | - UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> | - PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> | + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | + PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] | FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable @@ -964,7 +964,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <varlistentry id="sql-createtable-parms-unique"> <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term> - <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal> + <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal> <optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term> <listitem> @@ -979,6 +979,27 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM </para> <para> + If the <literal>WITHOUT OVERLAPS</literal> 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 + <literal>WITHOUT OVERLAPS</literal> column. (This is sometimes called a + temporal key, if the column is a range of dates or timestamps, but + <productname>PostgreSQL</productname> allows ranges over any base type.) + In effect, such a constraint is enforced with an <literal>EXCLUDE</literal> + constraint rather than a <literal>UNIQUE</literal> constraint. So for + example <literal>UNIQUE (id, valid_at WITHOUT OVERLAPS)</literal> behaves + like <literal>EXCLUDE USING GIST (id WITH =, valid_at WITH + &&)</literal>. The <literal>WITHOUT OVERLAPS</literal> column + must have a range or multirange type. Empty ranges/multiranges are + not permitted. The non-<literal>WITHOUT OVERLAPS</literal> 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 <xref linkend="btree-gist"/> extension (which + is the expected way to use this feature). + </para> + + <para> For the purpose of a unique constraint, null values are not considered equal, unless <literal>NULLS NOT DISTINCT</literal> is specified. @@ -1000,8 +1021,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <para> 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 <literal>WITHOUT OVERLAPS</literal> clause, it + will use a GiST index. The created index has the same name as the + unique constraint. </para> <para> @@ -1019,7 +1042,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <varlistentry id="sql-createtable-parms-primary-key"> <term><literal>PRIMARY KEY</literal> (column constraint)</term> - <term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal> + <term><literal>PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] )</literal> <optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term> <listitem> <para> @@ -1054,8 +1077,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <para> Adding a <literal>PRIMARY KEY</literal> 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 <literal>WITHOUT OVERLAPS</literal> was + specified. </para> <para> 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 01b43cc6a84..78e59384d1c 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 33759056e37..b2b3ecb5244 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1394,7 +1394,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, oldInfo->ii_NullsNotDistinct, false, /* not ready for inserts */ true, - indexRelation->rd_indam->amsummarizing); + indexRelation->rd_indam->amsummarizing, + oldInfo->ii_WithoutOverlaps); /* * Extract the list of column names and the column numbers for the new @@ -1874,6 +1875,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 +1899,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 +1982,7 @@ index_constraint_create(Relation heapRelation, islocal, inhcount, noinherit, + is_without_overlaps, is_internal); /* @@ -2427,7 +2432,8 @@ BuildIndexInfo(Relation index) indexStruct->indnullsnotdistinct, indexStruct->indisready, false, - index->rd_indam->amsummarizing); + index->rd_indam->amsummarizing, + indexStruct->indisexclusion && indexStruct->indisunique); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) @@ -2486,7 +2492,8 @@ BuildDummyIndexInfo(Relation index) indexStruct->indnullsnotdistinct, indexStruct->indisready, false, - index->rd_indam->amsummarizing); + index->rd_indam->amsummarizing, + indexStruct->indisexclusion && indexStruct->indisunique); /* fill in attribute numbers */ for (i = 0; i < numAtts; i++) @@ -3224,7 +3231,6 @@ IndexCheckExclusion(Relation heapRelation, indexInfo->ii_PredicateState = NULL; } - /* * validate_index - support code for concurrent index builds * 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 b987e023849..d7b71b81d3b 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" @@ -88,6 +89,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); @@ -146,6 +148,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 @@ -175,7 +178,8 @@ bool CheckIndexCompatible(Oid oldId, const char *accessMethodName, const List *attributeList, - const List *exclusionOpNames) + const List *exclusionOpNames, + bool isWithoutOverlaps) { bool isconstraint; Oid *typeIds; @@ -239,7 +243,7 @@ CheckIndexCompatible(Oid oldId, */ indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes, accessMethodId, NIL, NIL, false, false, - false, false, amsummarizing); + false, false, amsummarizing, isWithoutOverlaps); typeIds = palloc_array(Oid, numberOfAttributes); collationIds = palloc_array(Oid, numberOfAttributes); opclassIds = palloc_array(Oid, numberOfAttributes); @@ -250,8 +254,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)); @@ -561,6 +565,7 @@ DefineIndex(Oid tableId, bool amcanorder; bool amissummarizing; amoptions_function amoptions; + bool exclusion; bool partitioned; bool safe_index; Datum reloptions; @@ -681,6 +686,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) { @@ -849,7 +860,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", @@ -864,11 +875,16 @@ 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", accessMethodName))); + if (stmt->iswithoutoverlaps && strcmp(accessMethodName, "gist") != 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("access method \"%s\" does not support WITHOUT OVERLAPS constraints", + accessMethodName))); amcanorder = amRoutine->amcanorder; amoptions = amRoutine->amoptions; @@ -905,7 +921,8 @@ DefineIndex(Oid tableId, stmt->nulls_not_distinct, !concurrent, concurrent, - amissummarizing); + amissummarizing, + stmt->iswithoutoverlaps); typeIds = palloc_array(Oid, numberOfAttributes); collationIds = palloc_array(Oid, numberOfAttributes); @@ -917,8 +934,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. @@ -936,7 +954,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; @@ -990,10 +1008,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\"", @@ -1032,12 +1050,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); @@ -1046,7 +1064,7 @@ DefineIndex(Oid tableId, found = true; break; } - else if (stmt->excludeOpNames) + else if (exclusion) { /* * We found a match, but it's not an equality @@ -1190,6 +1208,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, @@ -1856,6 +1876,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo, Oid accessMethodId, bool amcanorder, bool isconstraint, + bool iswithoutoverlaps, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel) @@ -1879,6 +1900,23 @@ ComputeIndexAttrs(IndexInfo *indexInfo, else nextExclOp = NULL; + /* + * If this is a WITHOUT OVERLAPS constraint, we need space for exclusion + * ops, but we don't need to parse anything, so we can let nextExclOp be + * NULL. Note that for partitions/inheriting/LIKE, exclusionOpNames will + * be set, so we already allocated above. + */ + if (iswithoutoverlaps) + { + if (exclusionOpNames == NIL) + { + indexInfo->ii_ExclusionOps = palloc_array(Oid, nkeycols); + indexInfo->ii_ExclusionProcs = palloc_array(Oid, nkeycols); + indexInfo->ii_ExclusionStrats = palloc_array(uint16, nkeycols); + } + nextExclOp = NULL; + } + if (OidIsValid(ddl_userid)) GetUserIdAndSecContext(&save_userid, &save_sec_context); @@ -2158,6 +2196,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 @@ -2389,6 +2442,83 @@ GetDefaultOpClass(Oid type_id, Oid am_id) } /* + * 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() * * Create a name for an implicitly created index, sequence, constraint, diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index b3cc6f8f690..818ed5702cf 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -10100,6 +10100,7 @@ addFkRecurseReferenced(List **wqueue, Constraint *fkconstraint, Relation rel, conislocal, /* islocal */ coninhcount, /* inhcount */ connoinherit, /* conNoInherit */ + false, /* conPeriod */ false); /* is_internal */ ObjectAddressSet(address, ConstraintRelationId, constrOid); @@ -10398,6 +10399,7 @@ addFkRecurseReferencing(List **wqueue, Constraint *fkconstraint, Relation rel, false, 1, false, + false, /* conPeriod */ false); /* @@ -10920,6 +10922,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 */ @@ -14100,7 +14103,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 403a3f40551..f9a2fac79e4 100644 --- a/src/backend/executor/execIndexing.c +++ b/src/backend/executor/execIndexing.c @@ -114,6 +114,8 @@ #include "executor/executor.h" #include "nodes/nodeFuncs.h" #include "storage/lmgr.h" +#include "utils/multirangetypes.h" +#include "utils/rangetypes.h" #include "utils/snapmgr.h" /* waitMode argument to check_exclusion_or_unique_constraint() */ @@ -141,6 +143,8 @@ static bool index_unchanged_by_update(ResultRelInfo *resultRelInfo, Relation indexRelation); static bool index_expression_changed_walker(Node *node, Bitmapset *allUpdatedCols); +static void ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval, + char typtype, Oid atttypid); /* ---------------------------------------------------------------- * ExecOpenIndices @@ -211,7 +215,7 @@ ExecOpenIndices(ResultRelInfo *resultRelInfo, bool speculative) * detection in logical replication, 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; @@ -726,6 +730,32 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index, } /* + * If this is a WITHOUT OVERLAPS constraint, we must also forbid empty + * ranges/multiranges. This must happen before we look for NULLs below, or + * a UNIQUE constraint could insert an empty range along with a NULL + * scalar part. + */ + if (indexInfo->ii_WithoutOverlaps) + { + /* + * Look up the type from the heap tuple, but check the Datum from the + * index tuple. + */ + AttrNumber attno = indexInfo->ii_IndexAttrNumbers[indnkeyatts - 1]; + + if (!isnull[indnkeyatts - 1]) + { + TupleDesc tupdesc = RelationGetDescr(heap); + Form_pg_attribute att = TupleDescAttr(tupdesc, attno - 1); + TypeCacheEntry *typcache = lookup_type_cache(att->atttypid, 0); + + ExecWithoutOverlapsNotEmpty(heap, att->attname, + values[indnkeyatts - 1], + typcache->typtype, att->atttypid); + } + } + + /* * If any of the input values are NULL, and the index uses the default * nulls-are-distinct mode, the constraint check is assumed to pass (i.e., * we assume the operators are strict). Otherwise, we interpret the @@ -1102,3 +1132,37 @@ index_expression_changed_walker(Node *node, Bitmapset *allUpdatedCols) return expression_tree_walker(node, index_expression_changed_walker, (void *) allUpdatedCols); } + +/* + * ExecWithoutOverlapsNotEmpty - raise an error if the tuple has an empty + * range or multirange in the given attribute. + */ +static void +ExecWithoutOverlapsNotEmpty(Relation rel, NameData attname, Datum attval, char typtype, Oid atttypid) +{ + bool isempty; + RangeType *r; + MultirangeType *mr; + + switch (typtype) + { + case TYPTYPE_RANGE: + r = DatumGetRangeTypeP(attval); + isempty = RangeIsEmpty(r); + break; + case TYPTYPE_MULTIRANGE: + mr = DatumGetMultirangeTypeP(attval); + isempty = MultirangeIsEmpty(mr); + break; + default: + elog(ERROR, "WITHOUT OVERLAPS column \"%s\" is not a range or multirange", + NameStr(attname)); + } + + /* Report a CHECK_VIOLATION */ + if (isempty) + ereport(ERROR, + (errcode(ERRCODE_CHECK_VIOLATION), + errmsg("empty WITHOUT OVERLAPS value found in column \"%s\" in relation \"%s\"", + NameStr(attname), RelationGetRelationName(rel)))); +} diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 61ac172a857..9cac3c1c27b 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -760,7 +760,8 @@ make_ands_implicit(Expr *clause) IndexInfo * makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, List *predicates, bool unique, bool nulls_not_distinct, - bool isready, bool concurrent, bool summarizing) + bool isready, bool concurrent, bool summarizing, + bool withoutoverlaps) { IndexInfo *n = makeNode(IndexInfo); @@ -775,6 +776,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, n->ii_IndexUnchanged = false; n->ii_Concurrent = concurrent; n->ii_Summarizing = summarizing; + n->ii_WithoutOverlaps = withoutoverlaps; /* summarizing indexes cannot contain non-key attributes */ Assert(!summarizing || (numkeyattrs == numattrs)); diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 82f031f4cfe..b913f91ff03 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -828,7 +828,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"))); @@ -853,6 +853,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 84cef57a707..c8b4e8dde4c 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -531,7 +531,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 <list> rowsfrom_item rowsfrom_list opt_col_def_list -%type <boolean> opt_ordinality +%type <boolean> opt_ordinality opt_without_overlaps %type <list> ExclusionConstraintList ExclusionConstraintElem %type <list> func_arg_list func_arg_list_opt %type <node> func_arg_expr @@ -4141,7 +4141,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); @@ -4150,11 +4150,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; @@ -4175,7 +4176,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); @@ -4183,11 +4184,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; @@ -4309,6 +4311,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 79cad4ab30c..15274607542 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1555,6 +1555,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; @@ -1604,7 +1605,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); @@ -2157,6 +2160,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; @@ -2249,6 +2253,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), @@ -2385,7 +2394,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) * For UNIQUE and PRIMARY KEY, we just have a list of column names. * * Make sure referenced keys exist. If we are making a PRIMARY KEY index, - * also make sure they are NOT NULL. + * also make sure they are NOT NULL. For WITHOUT OVERLAPS constraints, we + * make sure the last part is a range or multirange. */ else { @@ -2397,6 +2407,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) ColumnDef *column = NULL; ListCell *columns; IndexElem *iparam; + Oid typid = InvalidOid; /* Make sure referenced column exists. */ foreach(columns, cxt->columns) @@ -2408,6 +2419,9 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) break; } } + if (!found) + column = NULL; + if (found) { /* @@ -2463,6 +2477,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) if (strcmp(key, inhname) == 0) { found = true; + typid = inhattr->atttypid; /* * It's tempting to set forced_not_null if the @@ -2512,6 +2527,50 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) } } + /* + * The WITHOUT OVERLAPS part (if any) must be a range or + * multirange type. + */ + if (constraint->without_overlaps && lc == list_last_cell(constraint->keys)) + { + if (!found && cxt->isalter) + { + /* + * Look up the column type on existing table. If we can't + * find it, let things fail in DefineIndex. + */ + Relation rel = cxt->rel; + + for (int i = 0; i < rel->rd_att->natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(rel->rd_att, i); + const char *attname; + + if (attr->attisdropped) + break; + + attname = NameStr(attr->attname); + if (strcmp(attname, key) == 0) + { + found = true; + typid = attr->atttypid; + break; + } + } + } + if (found) + { + if (!OidIsValid(typid) && column) + typid = typenameTypeId(NULL, column->typeName); + + if (!OidIsValid(typid) || !(type_is_range(typid) || type_is_multirange(typid))) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" in WITHOUT OVERLAPS is not a range or multirange type", key), + parser_errposition(cxt->pstate, constraint->location))); + } + } + /* OK, add it to the index definition */ iparam = makeNode(IndexElem); iparam->name = pstrdup(key); @@ -2537,6 +2596,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 badbf111ee0..c05d41ce023 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2403,6 +2403,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 63efc55f09e..5b6b7b809c0 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 546e7e4ce1a..6e07984e8d5 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -7412,6 +7412,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_conname, i_condeferrable, i_condeferred, + i_conperiod, i_contableoid, i_conoid, i_condef, @@ -7493,10 +7494,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 >= 180000) + appendPQExpBufferStr(query, + "c.conperiod "); + else + appendPQExpBufferStr(query, + "NULL AS conperiod "); /* * The point of the messy-looking outer join is to find a constraint that @@ -7564,6 +7572,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"); @@ -7671,6 +7680,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; @@ -17102,6 +17112,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 0b7d21b2e9b..9f907ed5ad4 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -475,6 +475,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 5bcc2244d58..ab6c8304913 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..faabecbc76f 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 >= 180000) + 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/catversion.h b/src/include/catalog/catversion.h index d46a9efe908..fe92cdd632f 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -57,6 +57,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202409171 +#define CATALOG_VERSION_NO 202409172 #endif 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/execnodes.h b/src/include/nodes/execnodes.h index 516b9487435..88467977f89 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -204,6 +204,7 @@ typedef struct IndexInfo bool ii_Concurrent; bool ii_BrokenHotChain; bool ii_Summarizing; + bool ii_WithoutOverlaps; int ii_ParallelWorkers; Oid ii_Am; void *ii_AmCache; diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h index 5209d3de89c..0765e5c57b4 100644 --- a/src/include/nodes/makefuncs.h +++ b/src/include/nodes/makefuncs.h @@ -98,7 +98,7 @@ extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions, List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent, - bool summarizing); + bool summarizing, bool withoutoverlaps); extern Node *makeStringConst(char *str, int location); extern DefElem *makeDefElem(char *name, Node *arg, int location); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index d6f7e795fe1..7e58db25268 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2744,6 +2744,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; @@ -3367,6 +3368,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..0fe3949f746 --- /dev/null +++ b/src/test/regress/expected/without_overlaps.out @@ -0,0 +1,1336 @@ +-- 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 WITHO... + ^ +-- 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: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type +LINE 4: CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHO... + ^ +-- 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 O... + ^ +-- 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: column "valid_at" in WITHOUT OVERLAPS is not a range or multirange type +LINE 4: CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT O... + ^ +-- 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; +-- rejects empty: +BEGIN; + INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); + ALTER TABLE temporal_rng ADD CONSTRAINT temporal_rng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng" +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). +-- rejects empty: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng" +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). +-- rejects empty: +UPDATE temporal_rng +SET id = '[1,2)', + valid_at = 'empty' +WHERE id = '[21,22)'; +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng" +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; +-- rejects empty: +BEGIN; + INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); + ALTER TABLE temporal_rng3 ADD CONSTRAINT temporal_rng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" +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)). +-- rejects empty: +INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" +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)). +-- rejects empty: +UPDATE temporal_rng3 +SET valid_at = 'empty' +WHERE id = '[1,2)' AND valid_at IS NULL; +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" +-- still rejects empty when scalar part is NULL: +UPDATE temporal_rng3 +SET id = NULL, + valid_at = 'empty' +WHERE id = '[1,2)' AND valid_at IS NULL; +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_rng3" +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; +-- rejects empty: +BEGIN; + INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); + ALTER TABLE temporal_mltrng ADD CONSTRAINT temporal_mltrng_pk PRIMARY KEY (id, valid_at WITHOUT OVERLAPS); +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng" +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). +-- rejects empty: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng" +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). +-- rejects empty: +UPDATE temporal_mltrng +SET id = '[1,2)', + valid_at = '{}' +WHERE id = '[21,22)'; +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng" +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; +-- rejects empty: +BEGIN; + INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); + ALTER TABLE temporal_mltrng3 ADD CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS); +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng3" +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)}). +-- rejects empty: +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng3" +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)}). +-- rejects empty: +UPDATE temporal_mltrng3 +SET valid_at = '{}' +WHERE id = '[1,2)' AND valid_at IS NULL; +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng3" +-- still rejects empty when scalar part is NULL: +UPDATE temporal_mltrng3 +SET id = NULL, + valid_at = '{}' +WHERE id = '[1,2)' AND valid_at IS NULL; +ERROR: empty WITHOUT OVERLAPS value found in column "valid_at" in relation "temporal_mltrng3" +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: ranges +-- +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; +-- +-- ON CONFLICT: multiranges +-- +TRUNCATE temporal_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING; +SELECT * FROM temporal_mltrng 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_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(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_mltrng ORDER BY id, valid_at; + id | valid_at +-------+--------------------------- + [1,2) | {[2000-01-01,2010-01-01)} +(1 row) + +TRUNCATE temporal_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING; +SELECT * FROM temporal_mltrng 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_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(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_mltrng ORDER BY id, valid_at; + id | valid_at +-------+--------------------------- + [1,2) | {[2000-01-01,2010-01-01)} +(1 row) + +TRUNCATE temporal_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_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_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_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_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO UPDATE SET id = EXCLUDED.id + '[4,5)'; +ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + id | valid_at +-------+--------------------------- + [1,2) | {[2000-01-01,2010-01-01)} +(1 row) + +-- with a UNIQUE constraint: +CREATE TABLE temporal_mltrng3 ( + id int4range, + valid_at datemultirange, + CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +TRUNCATE temporal_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING; +SELECT * FROM temporal_mltrng3 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_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(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_mltrng3 ORDER BY id, valid_at; + id | valid_at +-------+--------------------------- + [1,2) | {[2000-01-01,2010-01-01)} +(1 row) + +TRUNCATE temporal_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING; +SELECT * FROM temporal_mltrng3 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_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(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_mltrng3 ORDER BY id, valid_at; + id | valid_at +-------+--------------------------- + [1,2) | {[2000-01-01,2010-01-01)} +(1 row) + +TRUNCATE temporal_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_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 temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_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 temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO UPDATE SET id = EXCLUDED.id + '[4,5)'; +ERROR: ON CONFLICT DO UPDATE not supported with exclusion constraints +SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; + id | valid_at +-------+--------------------------- + [1,2) | {[2000-01-01,2010-01-01)} +(1 row) + +DROP TABLE temporal_mltrng3; +RESET datestyle; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 7a5a910562e..4f38104ba01 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..e05fa1d00c0 --- /dev/null +++ b/src/test/regress/sql/without_overlaps.sql @@ -0,0 +1,923 @@ +-- 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; +-- rejects empty: +BEGIN; + INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); + 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); +-- rejects empty: +INSERT INTO temporal_rng (id, valid_at) VALUES ('[3,4)', 'empty'); +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)'; +-- rejects empty: +UPDATE temporal_rng +SET id = '[1,2)', + valid_at = 'empty' +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; +-- rejects empty: +BEGIN; + INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); + 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')); +-- rejects empty: +INSERT INTO temporal_rng3 (id, valid_at) VALUES ('[3,4)', 'empty'); +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; +-- rejects empty: +UPDATE temporal_rng3 +SET valid_at = 'empty' +WHERE id = '[1,2)' AND valid_at IS NULL; +-- still rejects empty when scalar part is NULL: +UPDATE temporal_rng3 +SET id = NULL, + valid_at = 'empty' +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; +-- rejects empty: +BEGIN; + INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); + 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); +-- rejects empty: +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[3,4)', '{}'); +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)'; +-- rejects empty: +UPDATE temporal_mltrng +SET id = '[1,2)', + valid_at = '{}' +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; +-- rejects empty: +BEGIN; + INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); + 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'))); +-- rejects empty: +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[3,4)', '{}'); +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; +-- rejects empty: +UPDATE temporal_mltrng3 +SET valid_at = '{}' +WHERE id = '[1,2)' AND valid_at IS NULL; +-- still rejects empty when scalar part is NULL: +UPDATE temporal_mltrng3 +SET id = NULL, + valid_at = '{}' +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: ranges +-- + +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; + +-- +-- ON CONFLICT: multiranges +-- + +TRUNCATE temporal_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING; +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + +TRUNCATE temporal_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING; +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + +TRUNCATE temporal_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO NOTHING; +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + +TRUNCATE temporal_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)'; +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + +TRUNCATE temporal_mltrng; +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO UPDATE SET id = EXCLUDED.id + '[2,3)'; +-- id matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO UPDATE SET id = EXCLUDED.id + '[3,4)'; +-- date matches but no conflict +INSERT INTO temporal_mltrng (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng_pk DO UPDATE SET id = EXCLUDED.id + '[4,5)'; +SELECT * FROM temporal_mltrng ORDER BY id, valid_at; + +-- with a UNIQUE constraint: + +CREATE TABLE temporal_mltrng3 ( + id int4range, + valid_at datemultirange, + CONSTRAINT temporal_mltrng3_uq UNIQUE (id, valid_at WITHOUT OVERLAPS) +); +TRUNCATE temporal_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT DO NOTHING; +SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; + +TRUNCATE temporal_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO NOTHING; +SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; + +TRUNCATE temporal_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING; +-- id matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING; +-- date matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO NOTHING; +SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; + +TRUNCATE temporal_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(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_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT (id, valid_at) DO UPDATE SET id = EXCLUDED.id + '[4,5)'; +SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; + +TRUNCATE temporal_mltrng3; +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2000-01-01', '2010-01-01'))); +-- with a conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO UPDATE SET id = EXCLUDED.id + '[2,3)'; +-- id matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[1,2)', datemultirange(daterange('2010-01-01', '2020-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO UPDATE SET id = EXCLUDED.id + '[3,4)'; +-- date matches but no conflict +INSERT INTO temporal_mltrng3 (id, valid_at) VALUES ('[2,3)', datemultirange(daterange('2005-01-01', '2006-01-01'))) ON CONFLICT ON CONSTRAINT temporal_mltrng3_uq DO UPDATE SET id = EXCLUDED.id + '[4,5)'; +SELECT * FROM temporal_mltrng3 ORDER BY id, valid_at; + +DROP TABLE temporal_mltrng3; + +RESET datestyle; |