The list of all the queries

Mixing different mechanisms to generate surrogate values

Query 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 about the query: 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.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: High (Few or no false-positive results)
Query license: MIT License
Fixing suggestion: Choose one mechanism (SERIAL, identity columns) and use it consistently.
Data source: INFORMATION_SCHEMA only
SQL query: Click on query to copy it

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 where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
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.

Reference materials for further reading

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

The list of all the queries