Goal Find where a hierarchical structure is implemented in a base table by having a foreign key that refers to a candidate key of the same table. This design is called adjacency list.
Notes The query searches tables that implement an hierarchy based on the adjacency list pattern. The query is based only on the INFORMATION_SCHEMA views. A problem is that there could be multiple foreign key constraints with the same name in the same schema. It could make the result incorrect.
Type General (Overview of some aspect of the database.)
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA only
SQL Query 
SELECT DISTINCT rc.constraint_schema AS table_schema, fk_table.table_name, rc.constraint_name 
FROM INFORMATION_SCHEMA.referential_constraints AS rc INNER JOIN INFORMATION_SCHEMA.key_column_usage AS fk_table USING (constraint_schema, constraint_name)
INNER JOIN INFORMATION_SCHEMA.constraint_table_usage AS pk_table ON (rc.unique_constraint_schema=pk_table.constraint_schema AND rc.unique_constraint_name=pk_table.constraint_name)
WHERE fk_table.table_schema=pk_table.table_schema 
AND fk_table.table_name=pk_table.table_name 
AND rc.constraint_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 rc.constraint_schema, fk_table.table_name;

Categories

This query is classified under the following categories:

Database design antipatterns

Queries of this category provide information about possible occurrences of SQL database design antipatterns.

Hierarchical data

Queries of this catergory provide information about storing hierarchical data in the database.

Relationships between tables

Queries 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 tables

Queries of this category provide information about the structuring of base tables at the database conceptual level

NameDescription
Database design antipatternsQueries of this category provide information about possible occurrences of SQL database design antipatterns.
Hierarchical dataQueries of this catergory provide information about storing hierarchical data in the database.
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

Further reading and related materials:

This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 3: Naive Trees.
https://blog.duncanworthy.me/sql/hierarchical-data-pt1-adjacency-list/
Smell "Adjacency list": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).
Representing the Status of the Main Entities of an Information System in SQL Databases, Master Thesis, in Estonian.
Reference
This is one of the antipatterns from the Bill Karwin's book of SQL antipatterns. See Chapter 3: Naive Trees.
https://blog.duncanworthy.me/sql/hierarchical-data-pt1-adjacency-list/
Smell "Adjacency list": Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: Proceedings of the 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55-64. ACM, (2018).
Representing the Status of the Main Entities of an Information System in SQL Databases, Master Thesis, in Estonian.