On Sun, 12 May 2024 at 05:26, Paul Jungwirth <p...@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
