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

Copy SQL

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;