Goal 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)
Reliability High (Few or no false-positive results)
License MIT (opens in new tab)
Fixing Suggestion 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:

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 .

NameDescription
Find problems automaticallyQueries, 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:

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.

NameDescription
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
Default valueQueries of this catergory provide information about the use of default values.
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
Sequence generatorsQueries of this category provide information about sequence generators and their usage.

Further reading and related materials:

The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code)
Reference
The corresponding code smell in case of cleaning code is "G11: Inconsistency". (Robert C. Martin, Clean Code)