Find column descriptions that are candidates for describing a domain.
Notes
The result contains a set of column properties only if there are at least two non-foreign key columns that have at least one CHECK constraint and that have the same set of base type, character length, default value, nullability, and collation. The query considers only columns that have not been defined based on 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
General (Overview of some aspect of the database.)
WITH cols AS (SELECT c.table_schema, c.table_name, c.column_name, c.column_default, c.collation_name, c.is_nullable,
CASE WHEN c.data_type LIKE '%char%' THEN
c.data_type || '(' || c.character_maximum_length || ')'
ELSE c.data_type
END AS data_type
FROM INFORMATION_SCHEMA.columns AS c
WHERE c.domain_name IS NULL AND (table_schema, table_name) IN (SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.tables AS t WHERE table_type='BASE TABLE') AND
table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata AS s
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)),
fk_cols AS (SELECT A.table_schema, A.table_name , A.column_name
FROM information_schema.key_column_usage A
INNER JOIN information_schema.table_constraints B
ON A.table_schema=B.table_schema AND A.table_name=B.table_name AND A.constraint_name=B.constraint_name
INNER JOIN information_schema.columns C
ON A.table_schema=C.table_schema AND A.table_name=C.table_name AND A.column_name=C.column_name
INNER JOIN information_schema.schemata D
ON A.table_schema=D.schema_name
WHERE B.constraint_type='FOREIGN KEY'
AND (A.table_schema = 'public'
OR D.schema_owner<>'postgres')),
checks AS (
SELECT
(SELECT nspname FROM pg_namespace WHERE oid=c.relnamespace) AS table_schema,
c.relname as table_name,
a.attname AS column_name
FROM pg_constraint o INNER JOIN pg_class c ON c.oid = o.conrelid
INNER JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = o.conkey[1] AND a.attisdropped = FALSE
INNER JOIN pg_type t ON a.atttypid=t.oid
WHERE cardinality(o.conkey)=1 AND o.contype = 'c')
SELECT string_agg(c.table_schema ||'.'|| c.table_name ||'.'|| c.column_name,'; ' ORDER BY c.table_schema, c.table_name, c.column_name) AS columns, data_type, is_nullable, column_default, collation_name, count(*) AS number_of_columns
FROM cols AS c
WHERE NOT EXISTS (SELECT 1
FROM fk_cols AS f
WHERE c.table_schema=f.table_schema AND c.table_name=f.table_name AND c.column_name=f.column_name)
AND EXISTS (SELECT 1
FROM checks AS cs
WHERE c.table_schema=cs.table_schema AND c.table_name=cs.table_name AND c.column_name=cs.column_name)
GROUP BY data_type, is_nullable, column_default, collation_name
HAVING count(*)>1
ORDER BY Count(*) DESC, data_type, is_nullable DESC, column_default, collation_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems by overview
Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .
Categories
This query is classified under the following categories:
Name
Description
Comfortability of database evolution
Queries of this category provide information about the means that influence database evolution.
Domains
Queries of this category provide information about reusable specifications of column properties.