Thread

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

Laurenz Albe <laurenz.albe@cybertec.at> — 2025-10-29T13:52:38Z

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