1

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?

Volodymyr Bobyr
  • 329
  • 2
  • 12
  • No, not possible as of Postgres 14 (and I don't see anything for Postgres 15 either) –  Feb 03 '22 at 16:59
  • Not available as of PG 14. see https://stackoverflow.com/questions/61944643/does-postgresql-only-support-stored-generated-columns – Anand Sowmithiran Feb 03 '22 at 17:02

1 Answers1

2

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 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? – Volodymyr Bobyr Feb 03 '22 at 17:18