The list of all the queries

Do not leave out the referential constraints (based on adjacency list design)

Query goal: Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers.
Query type: Problem detection (Each row in the result could represent a flaw in the design)
Query reliability: Medium (Medium number of false-positive results)
Query license: MIT License
Fixing suggestion: Declare the foreign key constraint that refers to a candidate key of the same table.
Data source: INFORMATION_SCHEMA+system catalog
SQL query: Click on query to copy it

WITH keys as (select 
o.conname,
(select nspname from pg_namespace where oid=m.relnamespace) as key_schema,
m.relname as key_table, 
m.oid as key_table_oid,
o.conkey AS key_col
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class m on m.oid = o.conrelid
where o.contype in ('u','p','f')  and o.conrelid in (select oid from pg_class c where c.relkind = 'r')),
keys_unnest as (select conname, key_schema, key_table, key_table_oid, key_col, key_col_num, ordin
from keys, unnest(keys.key_col) with ordinality as k(key_col_num, ordin)),
keys_with_names as (select key_schema as table_schema, key_table as table_name, a_key.attname as column_name
from keys_unnest k inner join pg_attribute a_key on k.key_col_num = a_key.attnum and k.key_table_oid = a_key.attrelid and a_key.attisdropped = false),
surrogate_key AS (SELECT c.table_schema, c.table_name , c.column_name
FROM information_schema.columns c
LEFT JOIN information_schema.domains d USING (domain_schema, domain_name)
INNER JOIN information_schema.schemata s
ON c.table_schema=s.schema_name
WHERE  (coalesce (c.column_default, d.domain_default) ILIKE '%nextval%'  OR c.is_identity='YES')
AND (c.table_schema = 'public'
OR s.schema_owner<>'postgres'))

SELECT table_schema, table_name, column_name,  data_type, character_maximum_length
FROM INFORMATION_SCHEMA.columns AS c INNER JOIN INFORMATION_SCHEMA.tables USING (table_schema, table_name)
WHERE column_name~*'parent'
AND table_type='BASE TABLE' 
AND NOT EXISTS (SELECT *
FROM keys_with_names AS kwn
WHERE kwn.table_schema=c.table_schema
AND kwn.table_name=c.table_name
AND kwn.column_name=c.column_name)
AND NOT EXISTS (SELECT *
FROM surrogate_key AS sk
WHERE sk.table_schema=c.table_schema
AND sk.table_name=c.table_name
AND sk.column_name=c.column_name)
AND table_schema NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
ORDER BY table_type, table_schema, table_name, ordinal_position;

Collections where the query belongs to

Collection nameCollection description
Find problems about base tablesA selection of queries that return information about the data types, field sizes, default values as well as general structure of base tables. Contains all the types of queries - problem detection, software measure, and general overview
Find problems automaticallyQueries, 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 nameCategory description
Database design antipatternsQueries of this category provide information about possible occurrences of SQL database design antipatterns.
Relationships between tablesQueries of this category provide information about how database tables are connected to each other and whether such connections have been explicitly defined and whether it has been done correctly.
Structure of base tablesQueries of this category provide information about the structuring of base tables at the database conceptual level

The list of all the queries