Thursday, March 5, 2026

Identity columns vs free-standing sequences in PostgreSQL

Are the columns typically used in primary key columns, defined as IDENTITY columns, or as standalone columns with a default value generated from a free-standing sequence?
SELECT table_name, column_name, is_identity, column_default
FROM information_schema.columns
WHERE table_schema = 'myschema'
AND column_name IN ('sekvnr', 'id')
AND table_name IN ('table1','table2','table3','table4',
                   'table5','table6',
                   'table7','table8')
ORDER BY table_name, column_name;
Result:
        table_name          |  column_name  | is_identity | column_default
----------------------------+---------------+-------------+----------------
 table1                     | id            | YES         |
 table2                     | id            | YES         |
 table3                     | sekvnr        | NO          |
 table4                     | id            | YES         |
 table5                     | id            | YES         |
 table6                     | id            | YES         |
 table7                     | id            | YES         |
 table8                     | id            | YES         |
(8 rows)
It's clear from the query that the only table that is still supported by a free-standing sequence to generate its primary key values, is table3!

No comments:

Post a Comment