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

  first_name text,
  last_name text

and a function:

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

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/

| improve this answer | |
  • 2
    This is the correct answer. See, for example, how Postgrest refers to this behavior as "computed columns". – fiatjaf Nov 8 '17 at 17:03
  • 1
    Typo, I think - the select should be of select people.full_name from people or select full_name(people) from people? – Barguast Dec 21 '17 at 17:16
  • No it works like that. The prefix in "select people.full_name from people" can be left out like in regular SQL. – Fabian Zeindl Dec 25 '17 at 22:06
  • 1
    Could you change the accepted answer then? – Fabian Zeindl May 26 '18 at 13:21
  • 1
    I could only get to work the above example with select people.full_name from people; and select full_name(people) from people. The version without a prefix select full_name from people; gives an error in PostgreSQL 11: SQL Error [42703]: ERROR: column "full_name" does not exist – Jan Katins Feb 26 '19 at 21:37

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.

| improve this answer | |
  • Rats. I suppose I could go for a materialised view if I need the performance. I have added a request for the feature, as it’s already available in the competition. – Manngo Mar 10 '17 at 8:48
  • 1
    No need for a MVIEW. A column with a trigger will also let you index the column's content – a_horse_with_no_name Mar 10 '17 at 8:50
  • I have a philosophical issue with storing additional real columns which are basically a repeat of the other data. It de-normalises the table. – Manngo Mar 13 '17 at 8:39
  • 5
    Well, a computed column is exactly that: storing de-normalized data. How the computed column's value is generated doesn't matter. I don't see a conceptual difference between a "real" computed column and one that is generated through a trigger – a_horse_with_no_name Mar 13 '17 at 8:56
  • Another workaround (for some cases) is to index an expression. – ypercubeᵀᴹ Jul 18 '17 at 11:51


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 


    height_cm NUMERIC,
    height_in NUMERIC GENERATED ALWAYS AS ( height_cm / 2.54 ) STORED


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


  • 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


| improve this answer | |
  • Thanks for that information. I see that version 12 is not yet fully released, but I am looking forward to it. I note that PostgreSQL uses the more standard syntax, but is otherwise the same as MSSQL. I found the SQL2003 specifications here: sigmodrecord.org/publications/sigmodRecord/0403/… . I have always said that SQL is a very slow moving standard, and DBMS implementations are even slow. – Manngo Jul 17 '19 at 4:42

Depending on your use-case, you could achieve this sort of behavior by declaring a new column and populating it with a trigger on insert/update.

I would use the above answers if possible to avoid duplicating data that could be derived form what you already have, but it does do the trick and could be useful for computationally intensive derived fields that you want to compute once and save.

I considered this approach to deal with an issue where I sometimes only had 15 digits of an 18-digit key (the last 3 digits are just a checksum) but wanted to be able to enforce a foreign-key relationship.

PG docs on triggers: https://www.postgresql.org/docs/9.6/sql-createtrigger.html

W3 example: https://www.w3resource.com/PostgreSQL/postgresql-triggers.php

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.