Re: SQL:2011 application time

On 5/12/24 05:55, Matthias van de Meent wrote:
>> > pg=# CREATE UNIQUE INDEX ON temporal_testing USING gist (id, valid_during);
>> > ERROR: access method "gist" does not support unique indexes
>>
>> To me that error message seems correct. The programmer hasn't said anything about the special
>> temporal behavior they are looking for.
>
> But I showed that I had a GIST index that does have the indisunique
> flag set, which shows that GIST does support indexes with unique
> semantics.
>
> That I can't use CREATE UNIQUE INDEX to create such an index doesn't
> mean the feature doesn't exist, which is what the error message
> implies.

True, the error message is not really telling the truth anymore. I do think most people who hit this
error are not thinking about temporal constraints at all though, and for non-temporal constraints it
is still true. It's also true for CREATE INDEX, since WITHOUT OVERLAPS is only available on the
*constraint*. So how about adding a hint, something like this?:

ERROR: access method "gist" does not support unique indexes
HINT: To create a unique constraint with non-overlap behavior, use ADD CONSTRAINT ... WITHOUT OVERLAPS.

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com