Re: SQL:2011 application time

On Sun, 12 May 2024 at 05:26, Paul Jungwirth
<pj@illuminatedcomputing.com> wrote:
> On 5/9/24 17:44, Matthias van de Meent wrote:
> > I haven't really been following this thread, but after playing around
> > a bit with the feature I feel there are new gaps in error messages. I
> > also think there are gaps in the functionality regarding the (lack of)
> > support for CREATE UNIQUE INDEX, and attaching these indexes to
> > constraints
> Thank you for trying this out and sharing your thoughts! I think these are good points about CREATE
> UNIQUE INDEX and then creating the constraint by handing it an existing index. This is something
> that I am hoping to add, but it's not covered by the SQL:2011 standard, so I think it needs some
> discussion, and I don't think it needs to go into v17.

Okay.

> For instance you are saying:
>
>  > 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.

> To get non-overlapping semantics from an index, this more
> explicit syntax seems better, similar to PKs in the standard:

Yes, agreed on that part.

>  > pg=# CREATE UNIQUE INDEX ON temporal_testing USING gist (id, valid_during WITHOUT OVERLAPS);
>  > ERROR:  access method "gist" does not support unique indexes
>
> We could also support *non-temporal* unique GiST indexes, particularly now that we have the stratnum
> support function. Those would use the syntax you gave, omitting WITHOUT OVERLAPS. But that seems
> like a separate effort to me.

No objection on that.

Kind regards,

Matthias van de Meent