public inbox for [email protected]
help / color / mirror / Atom feedFrom: Matthias van de Meent <[email protected]>
To: Paul Jungwirth <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: jian he <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: SQL:2011 application time
Date: Sun, 12 May 2024 14:55:52 +0200
Message-ID: <CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk++ey7xw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CACJufxHsYDfHK62FTHdLsgDenvJd_bEuxCiyH62Lacj7NvzX4w@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CA+TgmoaH-2fAttd24pA3p45TUHbM9hRU6ud=brhKsp9ENHXWDg@mail.gmail.com>
<[email protected]>
<CA+TgmobfDJuXJATNUco+g6AiUb3WS2Pi9FrJe9hKAHGw37RX-g@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAEze2WiD+U1BuJDLGL=FXxa8hDxNALVE6Jij0cNXjp10Q=nZHw@mail.gmail.com>
<[email protected]>
On Sun, 12 May 2024 at 05:26, Paul Jungwirth
<[email protected]> 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
view thread (10+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: SQL:2011 application time
In-Reply-To: <CAEze2Wh21V66udM8cbvBBsAgyQ_5x9nfR0d3sWzbmZk++ey7xw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox