Re: BUG #19098: Can't create unique gist index, where pg_indexes says that WITHOUT OVERLAPS does exacly that - Mailing list pgsql-bugs

On Wed, 29 Oct 2025 at 18:52, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Wed, 2025-10-29 at 17:43 +0500, Kirill Reshke wrote:
> > On Wed, 29 Oct 2025 at 16:31, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> > >
> > >
> > > I think I see what you mean:
> > >
> > >   CREATE TABLE temp (
> > >      id bigint NOT NULL,
> > >      valid tstzrange NOT NULL,
> > >      CONSTRAINT temp_pkey PRIMARY KEY (id, valid WITHOUT OVERLAPS)
> > >   );
> > >
> > >   SELECT pg_get_indexdef('temp_pkey'::regclass);
> > >
> > >                              pg_get_indexdef
> > >   ----------------------------------------------------------------------
> > >    CREATE UNIQUE INDEX temp_pkey ON laurenz.temp USING gist (id, valid)
> > >
> > > That CREATE INDEX statement won't work.
> > >
> > > Yours,
> > > Laurenz Albe
> > >
> > >
> >
> > Yep, this is probably a valid rewording of the first email. Is it a
> > problem that is worth fixing?
>
> I personally think so, although the fact that this occurs in the regression tests
> might mean that it is intentional.
>
> Anyway, here is a patch that fixes the problem for me.  I am not happy with it,
> because it hard-codes that CREATE UNIQUE INDEX only works with B-tree indexes,
> but I don't have a better idea.
>
> Yours,
> Laurenz Albe

indeed, this has been discussed [0]

After brief reading, it looks like this is just an oversight, CREATE
UNIQUE INDEX ... USING gist implementation was discussed but never
implemented...
Your fix WFM, but if we decide this is not the correct way to fix an
issue, should we at least document it?


[0] https://www.postgresql.org/message-id/37d3137d-6bdd-4192-9f8f-da35974aa693%40illuminatedcomputing.com

-- 
Best regards,
Kirill Reshke