Use the same mechanism of generating surrogate key values throughout the database. The use of SERIAL notation/explicitly creating a sequence generator and declaration of a column as an identity column will cause the creation of an external and internal sequence generator, respectively. Nevertheless, one should try to stick with using one of the mechanisms in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)
Notes
The query returns information about all the base table columns that have the identity property or the default value is found by using the nextval function if there is at least one base table column with the identity property and one base table column with the default value that is found by using the nextval function. The query takes into account a possibility that a column may be associated with a sequence generator through a domain.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Choose one mechanism (SERIAL, identity columns) and use it consistently.
Data Source
INFORMATION_SCHEMA only
SQL Query
WITH serial_columns AS (SELECT c.table_schema, c.table_name , c.column_name, coalesce (c.column_default, d.domain_default) AS column_default, is_identity
FROM information_schema.columns c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.schemata s
ON c.table_schema=s.schema_name
WHERE coalesce (c.column_default, d.domain_default) ILIKE '%nextval%'
AND (c.table_schema = 'public'
OR s.schema_owner<>'postgres')),
identity_columns AS (SELECT c.table_schema, c.table_name , c.column_name, c.column_default, is_identity
FROM information_schema.columns c
INNER JOIN information_schema.schemata s
ON c.table_schema=s.schema_name
WHERE c.is_identity='YES'
AND (c.table_schema = 'public'
OR s.schema_owner<>'postgres')),
surrogate_columns AS (SELECT table_schema, table_name , column_name, column_default, is_identity
FROM serial_columns
UNION ALL SELECT table_schema, table_name , column_name, column_default, is_identity
FROM identity_columns)
SELECT table_schema, table_name , column_name, column_default, is_identity
FROM surrogate_columns
WHERE EXISTS (SELECT 1 FROM serial_columns)
AND EXISTS (SELECT 1 FROM identity_columns)
ORDER BY is_identity, table_schema, table_name, column_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Default value
Queries of this catergory provide information about the use of default values.
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
Sequence generators
Queries of this category provide information about sequence generators and their usage.
Further reading and related materials:
Reference
The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code)