Thread

Re: Disallow whole-row index references with virtual generated columns?

Álvaro Herrera <alvherre@kurilemu.de> — 2026-05-08T19:30:31Z

On 2026-May-08, Matthias van de Meent wrote:

> On Fri, 8 May 2026 at 16:12, Álvaro Herrera <alvherre@kurilemu.de> wrote:

> > > This is what I tried on master:
> > >
> > >     CREATE TABLE t (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL);
> > >     CREATE UNIQUE INDEX t_a_wholerow_pred_idx ON t (a) WHERE t IS NOT NULL;
> > >     INSERT INTO t(a) VALUES (1);
> > >     INSERT INTO t(a) VALUES (1);  -- accepted, two rows with a = 1
> >
> > Hmm, but this also works just fine when the column b is a normal column,
> > so I don't see why you would want to restrict this specifically for
> > virtual generated columns.
> 
> Are you sure it works fine?

I only meant that the sequence that Ayush was saying should fail with
virtual columns and actually fails to fail, also fails to fail with
regular columns.  Therefore this is not a virtual column-specific
problem, but something more general that perhaps we have not studied
enough.

TBH I'm not really sure what's the usefulness of indexes over whole-row
types.  I imagine the use cases for them are rather infrequent, if not
outright non-existent.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/