Multiple columns in the same base table that are associated with a sequence generator

Query goal:

Find base tables where multiple columns are associated with a sequence generator. Do not create unnecessary sequence generators. If one uses in a table a surrogate key, then it is enough to have one column where the values are generated by using a (external or internal) sequence generator.

Notes about the query:

Finds base tables where more than one column is associated with a sequence generator. The query takes into account both external and internal (are created as the result of declaring a column as the identity column) sequence generators. The query takes into account a possibility that a column may be associated with an external sequence generator through a domain. In case of the string_agg function, the line break (br) tag is used as a part of the separator for the better readability in case the query result is displayed in a web browser.

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

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, c.ordinal_position, t.table_type, 'SERIAL' AS type
FROM information_schema.columns c LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.tables AS t USING (table_schema, table_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.ordinal_position, t.table_type, 'IDENTITY COLUMN' AS type
FROM information_schema.columns c
INNER JOIN information_schema.tables AS t USING (table_schema, table_name)
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, ordinal_position, table_type, type
FROM serial_columns
UNION ALL SELECT table_schema, table_name , column_name, ordinal_position, table_type, type
FROM identity_columns)
SELECT table_schema, table_name , table_type, Count(*) AS number_of_surrogate_columns, string_agg(column_name || ' ' || type, ';<br>' ORDER BY ordinal_position) AS surrogate_columns
FROM surrogate_columns
GROUP BY table_schema, table_name, table_type 
HAVING Count(*)>1
ORDER BY Count(*) DESC, table_schema, table_name;

Collections where the query belongs to

Collection name	Collection 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 .

Collection name

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

Category name	Category description
Default value	Queries of this catergory provide information about the use of default values.
Sequence generators	Queries of this category provide information about sequence generators and their usage.

Category name

Category description

Default value

Queries of this catergory provide information about the use of default values.

Sequence generators

Queries of this category provide information about sequence generators and their usage.