PostgreSQL has supported generated columns since version 12. Generated columns can be created easily with the CREATE TABLE and ALTER TABLE statements.

We can check whether a table contains a generated column or not with the following methods.

The \d Command

In psql, the \d command shows information about tables, views, materialised views, index, sequences, or foreign tables.

We can therefore use this command to check whether a given table has any generated columns:

\d students

Result:

                                                            Table "public.students"
  Column   |          Type          | Collation | Nullable |                                      Default                                      
-----------+------------------------+-----------+----------+-----------------------------------------------------------------------------------
 id        | integer                |           | not null | 
 firstname | character varying(50)  |           |          | 
 lastname  | character varying(50)  |           |          | 
 fullname  | character varying(101) |           |          | generated always as ((((firstname::text || ' '::text) || lastname::text))) stored
Indexes:
    "students_pkey" PRIMARY KEY, btree (id)

We can see that the fullname column is a generated column. We can see this, because the Default column contains its expression.

In this case, the expression looks like this:

generated always as ((((firstname::text || ' '::text) || lastname::text))) stored

That expression reflects the code that was used to create the generated column. We can see that it’s a stored column (as opposed to virtual).

• A stored generated column stores its value in the table (and therefore uses up space in the table).
• A virtual generated column generates its value dynamically when the table is read.

As of PostgreSQL 14, PostgreSQL only supports stored generated columns. This could change in a future release.

The information_schema.columns View

The information_schema.columns view contains information about columns:

SELECT 
  column_name, 
  is_generated,
  generation_expression
FROM information_schema.columns
WHERE table_name = 'students';

Result:

 column_name | is_generated |                 generation_expression                  
-------------+--------------+--------------------------------------------------------
 id          | NEVER        | 
 firstname   | NEVER        | 
 lastname    | NEVER        | 
 fullname    | ALWAYS       | (((firstname)::text || ' '::text) || (lastname)::text)

Here, I returned two columns of interest:

• The is_generated column tells us whether or not the column is generated. ALWAYS means that it is.
• The generation_expression shows us the expression that the generated column consists of.