The list of all the queries

Domain candidates

Query goal: Find column descriptions that are candidates for describing a domain.
Notes about the query: 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.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

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,';<br>' 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 where the query belongs to

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

Category nameCategory description
Comfortability of database evolutionQueries of this category provide information about the means that influence database evolution.
DomainsQueries of this category provide information about reusable specifications of column properties.

The list of all the queries