How do I list all identity columns in a table-CodePudding

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.

CodePudding user response：

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 * 
    from pg_attribute
   where attnum > 0 
     and attgenerated <> '' 
     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