Horton

Re: Virtual generated columns

Zhang Mingli <zmlpostgres@gmail.com>

View thread
From: Zhang Mingli <zmlpostgres@gmail.com>
To: Richard Guo <guofenglinux@gmail.com>, jian he <jian.universality@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>, Dean Rasheed <dean.a.rasheed@gmail.com>, Alexander Lakhin <exclusion@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2025-02-10T05:15:58Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Expand virtual generated columns for ALTER COLUMN TYPE

  2. Eliminate code duplication in replace_rte_variables callbacks

  3. Expand virtual generated columns in the planner

  4. Virtual generated columns

  5. Additional tests for stored generated columns

  6. Improve generated_stored test

  7. Fix handling of CREATE DOMAIN with GENERATED constraint syntax

  8. Add pg_constraint rows for not-null constraints

  9. Put generated_stored test objects in a schema

  10. Rename regress test generated to generated_stored

  11. Small code simplification

  12. Remove useless code

  13. Remove useless initializations

  14. doc: Clarify that pg_attrdef also stores generation expressions

  15. Clean out column-level pg_init_privs entries when dropping tables.

  16. Re-implement the ereport() macro using __VA_ARGS__.

On Feb 10, 2025 at 12:53 +0800, jian he <jian.universality@gmail.com>, wrote:
>
> please check attached.
>
> > BTW, I was curious about what happens if the replacement expression is
> > constant, so I tried running the query below.
> >
> > CREATE TABLE t (a int, b int GENERATED ALWAYS AS (1 + 1));
> > INSERT INTO t VALUES (1);
> > INSERT INTO t VALUES (2);
> >
> > # SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
> > a | b
> > ---+---
> > | 2
> > | 2
> > (2 rows)
> >
> > Is this the expected behavior? I was expecting that t2.b should be
> > all NULLs.
> >
> SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
> should be same as
> SELECT t2.a, 2 as b FROM t t1 LEFT JOIN t t2 ON FALSE;
> so i think this is expected.
Hi,

I believe virtual columns should behave like stored columns, except they don't actually use storage.
Virtual columns are computed when the table is read, and they should adhere to the same rules of join semantics.
I agree with Richard, the result seems incorrect. The right outcome should be:
gpadmin=# SELECT t2.a, t2.b FROM t t1 LEFT JOIN t t2 ON FALSE;
 a | b
------+------
 NULL | NULL
 NULL | NULL
(2 rows)


--
Zhang Mingli
HashData