0

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
  • 171
  • 1
  • 10

1 Answers1

1

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
  • 167,868
  • 16
  • 137
  • 184
  • 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