In PostgreSQL, a generated column is a special column that is always computed from other columns. A generated column doesn’t have a fixed value like in a base column. Rather, its value is determined by an expression that references other columns in the table.

Generated columns are included in the SQL standard (ISO/IEC 9075), and are supported by most major RDBMSs. Generated columns were first introduced in PostgreSQL 12.

Example

Let’s jump right in and create a table with a generated column in PostgreSQL:

CREATE TABLE Students (
  Id INTEGER PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  FullName VARCHAR(101) GENERATED ALWAYS AS (FirstName || ' ' || LastName) STORED
);

In this example, the last column is a generated column.

In PostgreSQL, generated columns must have GENERATED ALWAYS in their definition (this is usually optional in other RDBMSs).

Also, generated columns in PostgreSQL must have STORED at the end. This is to specify that the generated column is stored (as opposed to virtual – see the explanation below for more info). This is usually optional in other RDBMSs (they typically default to VIRTUAL).

Now that we’ve created a table with a generated column, let’s insert data:

INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0001, 'Lucy', 'Green');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0002, 'Aziz', 'Ahmad');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0003, 'Zohan', 'Ahuja');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0004, 'Homer', 'Presley');
INSERT INTO Students (Id, FirstName, LastName) 
VALUES (0005, 'Sally', 'Smith');

Notice that we only inserted values into the other columns. We did not insert values into the generated column.

Now let’s select data from the table:

SELECT * FROM Students;

Result:

 id | firstname | lastname |   fullname    
----+-----------+----------+---------------
  1 | Lucy      | Green    | Lucy Green
  2 | Aziz      | Ahmad    | Aziz Ahmad
  3 | Zohan     | Ahuja    | Zohan Ahuja
  4 | Homer     | Presley  | Homer Presley
  5 | Sally     | Smith    | Sally Smith

We can see that our generated column contains the concatenation of the FirstName and LastName columns (as specified in its definition).

The generated column’s expression doesn’t have to be a concatenation. For example, in another scenario it could calculate a discounted price, based on the original price in a price column, and a percentage discount in a discount column.

Stored vs Virtual

Generated columns can be either stored or virtual. Here’s the difference:

• STORED (aka PERSISTENT): Column values are stored in the table. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column.
• VIRTUAL:  A virtual generated column occupies no storage and is computed when it is read. A virtual column takes no storage.

At the time of writing PostgreSQL (14) only supports stored generated columns.

Restrictions

Generated columns have a number of restrictions. For example, a generation expression cannot reference another generated column. Also, they can only use immutable functions and cannot use subqueries or reference anything other than the current row in any way.

See PostgreSQL’s documentation for information other restrictions and more information on creating generated columns in PostgreSQL.