Postgres - Generated Not-Stored Column

Question

According to Postgres docs, one can create generated stored columns like so:

CREATE TABLE people (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

However, what if I want to have a column that is generated only when it is referenced and is not stored after use? Is that currently possible?

No, not possible as of Postgres 14 (and I don't see anything for Postgres 15 either) — user330315
– user330315, Commented Feb 3, 2022 at 16:59
Not available as of PG 14. see stackoverflow.com/questions/61944643/… — Anand Sowmithiran
– Anand Sowmithiran, Commented Feb 3, 2022 at 17:02

Laurenz Albe · Accepted Answer · 2022-02-03 17:51:22Z

5

That is not possible in PostgreSQL, but it is not necessary either. Simply create a view:

CREATE VIEW people_i8 AS
SELECT ...,
       height_cm,
       height_cm / 2.54 AS height_in
FROM people;

Such a view can also be the target of INSERT, UPDATE and DELETE, so it should work fine for you.

Since views don't persist data (essentially, a reference to the view is replaces with the defining query), height_in is calculated when the view is queried.

edited Feb 3, 2022 at 17:51

answered Feb 3, 2022 at 17:11

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Volodymyr Bobyr Over a year ago

Thanks! Do you know if views generate columns only when you reference them? I.e. if have a SELECT clause that does not reference the height_in column, will it still get generated?

Collectives™ on Stack Overflow

Postgres - Generated Not-Stored Column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related