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@illuminatedcomputing.com