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
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.
Type
Problem detection (Each row in the result could represent a flaw in the design)
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, '; ' 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
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
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.