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