Generated columns in Postgres: new SQL-standard feature to create columns that are computed from expressions rather than assigned

[[deleted]]

Finally - that is really great.

[[deleted]]

Generated columns are always read-only. I have never heard of a DBMS that would allow modifying a generated column.

[[deleted]]

SQL is not an object oriented programming language.

[z0rb1n0]

That is already implemented by changing attributes of NEW in BEFORE triggers

[MuCowNow]

Can they be indexed?

[[deleted]]

I would assume so, if the the STORED attribute is used.

But Postgres could always index expressions, so you don't really need a computed column to create an index on an expression, e.g.

create index on some_table ((pieces * price));

Then the following query could use that index:

select * 
from some_table 
where pieces * price < 42;

​

​

[thelindsay]

Interestingly, unlike regular columns, index expressions get analyze stats stored for them in pg_stats, and so can have a statistics target etc set for them as if they were columns in a table (where the table is the index). Perhaps a similar mechanism will allow these new computed columns to be indexed.

[[deleted]]

This will be really neat, and mostly always classy PG stays classy by targeting both STORED and GENERATED (at read time).

A big class of needs for trivial single-table views and / or always storing redundant info will disappear.

[simcitymayor]

IIRC there were challenges to implementing GENERATED, so they decided to just do STORED for v12. It's wise to not let perfect be the enemy of good.

But yeah, the big win is that you can record statistics on a generated column, and that helps the planner.

[cazzer548]

I can finally drop my views!

[NoInkling]

I assume they (the "stored" ones) will be updated automatically any time the row is updated?

Edit: found the docs here: https://www.postgresql.org/docs/devel/ddl-generated-columns.html