PostgreSQL: Generated Columns

Does PostgreSQL support generated columns? Also know as virtual columns. I am not talking about IDENTITY columns.

I can’t find any information on this remarkable feature but I know that it is available on SQL Server, and in the latest versions of MariaDB & MySQL.

The feature is mentioned in the SQL:2003 standard, and there was some discussion on the PostgreSQL forums around 2006, but I can’t find anything substantial on the matter.

There is some discussion on SO, but it is quite old now, so it may well be out of date.

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/

StackExchange Database Administrators Q#166792, answer score: 22