PostgreSQL: Generated Columns

Not sure if this is what you want, but attribute notation row.full_name and function notation full_name(row) are equivalent in postgresql.

That means you take a table

CREATE TABLE people (
  first_name text,
  last_name text
);

and a function:

CREATE FUNCTION full_name(people) RETURNS text AS $$
  SELECT $1.first_name || ' ' || $1.last_name;
$$ LANGUAGE SQL;

and call it like this:

select full_name from people

Is that what you need?

To speed up things you can create an expression index:

CREATE INDEX people_full_name_idx ON people
USING GIN (to_tsvector('english', full_name(people)));

Or store everything in a materialised view.

Example taken from here: http://bernardoamc.github.io/sql/2015/05/11/postgres-virtual-columns/


No, this is currently (as of Postgres 9.6) not supported.

The only workaround is to use a trigger or a view if it's a simple calculation that you do not need to index.


Yes: GENERATED ALWAYS AS … STORED

Postgres 12 adds the functionality for generated columns, as mentioned in the SQL:2003 standard.

The value is generated at the time of an INSERT or UPDATE, then stored with the row like any other value.

A generated must be based on a base column of the same table, or on an immutable function.

Syntax is simple, a clause on CREATE TABLE:

GENERATED ALWAYS AS ( generation_expr ) STORED 

Example:

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

Features:

  • Can be indexed.
  • Part of the SQL standard.

Caveats:

  • Based on columns of the same table (not related tables)
  • Not allowed for partitioning (cannot be part of a partition key)
  • Data always written to row, taking space in storage
    • Future feature might offer VIRTUAL for values calculated on-the-fly without storage
  • Single-generation deep (use base column, not another generated column)
  • There is no GENERATED BY DEFAULT (you cannot override value)
  • Cannot access gen-col in BEFORE trigger (value not yet determined)
  • Functions must be immutable

See:

  • Generated columns in PostgreSQL 12 
by Kirk Roybal (2019-07-05)
  • manual: 5.3. Generated Columns