How do I list all identity columns in a table

Question

What query should I use to list all GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY columns in given table in PostgreSQL database?

I would like also like to see whether the column is GENERATED ALWAYS or GENERATED BY DEFAULT.

Answer 1

You can get the list of all generated columns by looking in the pg_attribute table under the attgenerated column:

postgres=# create table abc (
    id int GENERATED ALWAYS AS IDENTITY,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED);

postgres=# select attname, attidentity, attgenerated 
             from pg_attribute 
            where attnum > 0 
              and attrelid = (select oid from pg_class where relname = 'abc');

  attname  | attidentity | attgenerated 
-----------+-------------+--------------
 id        | a           | 
 height_cm |             | 
 height_in |             | s
(3 rows)

Identity columns are identified in attidentity. More information in the PostgreSQL documentation