The list of all the queries

Do not always depend on one's parent

Query goal: Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table.
Notes about the query: The query searches tables that implement a hierarchy based on the adjacency list pattern.
Query type: General (Overview of some aspect of the database.)
Query license: MIT License
Data source: system catalog only
SQL query: Click on query to copy it

WITH fk as (select
o.conname,
(select nspname from pg_namespace where oid=f.relnamespace) as foreign_schema,
f.relname as foreign_table,
f.oid as foreign_table_oid,
o.confkey AS foreign_col,
(select nspname from pg_namespace where oid=c.relnamespace) as target_schema,
c.relname as target_table,
c.oid as target_table_oid,
o.conkey AS target_col,
CASE WHEN o.confupdtype='a' THEN 'NO ACTION'
WHEN o.confupdtype='r' THEN 'RESTRICT'
WHEN o.confupdtype='c' THEN 'CASCADE'
WHEN o.confupdtype='n' THEN 'SET NULL'
WHEN o.confupdtype='d' THEN 'SET DEFAULT' END AS on_update_cascade,
CASE WHEN o.confdeltype='a' THEN 'NO ACTION'
WHEN o.confdeltype='r' THEN 'RESTRICT'
WHEN o.confdeltype='c' THEN 'CASCADE'
WHEN o.confdeltype='n' THEN 'SET NULL'
WHEN o.confdeltype='d' THEN 'SET DEFAULT' END AS on_delete_cascade
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_class f on f.oid = o.confrelid
where o.contype = 'f'),
fk_unnest as (select conname, foreign_schema, foreign_table,  foreign_table_oid, foreign_col, foreign_col_num, target_schema, target_table, target_table_oid, target_col, target_col_num, ordin, on_update_cascade, on_delete_cascade
from fk, unnest(fk.foreign_col, fk. target_col) with ordinality as f(foreign_col_num, target_col_num, ordin)),
fk_with_names as (select conname, foreign_schema, foreign_table, array_agg(a_foreign.attname order by ordin) as foreign_col, target_schema, target_table, array_agg(a_target.attname order by ordin) as target_col, on_update_cascade, on_delete_cascade
from fk_unnest fk inner join pg_attribute a_foreign on fk.foreign_col_num = a_foreign.attnum and fk.foreign_table_oid = a_foreign.attrelid and a_foreign.attisdropped = false
inner join pg_attribute a_target on fk.target_col_num = a_target.attnum and fk.target_table_oid = a_target.attrelid and a_target.attisdropped = false
group by conname, foreign_schema, foreign_table, target_schema, target_table,on_update_cascade, on_delete_cascade)
select conname, target_schema as schema, target_table as table, foreign_col, target_col, on_update_cascade, on_delete_cascade
from fk_with_names
where foreign_schema=target_schema and foreign_table=target_table
order by target_schema, target_table, conname;

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

Reference materials for further reading

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/
Sharma, T., Fragkoulis, M., Rizou, S., Bruntink, M. and Spinellis, D.: Smelly relations: measuring and understanding database schema quality. In: 40th International Conference on Software Engineering: Software Engineering in Practice, pp. 55–64. ACM, (2018). https://doi.org/10.1145/3183519.3183529 (Adjacency list)
Representing the Status of the Main Entities of an Information System in SQL Databases, Master Thesis, in Estonian.
Factor, P.: SQL Code Smells. Redgate, http://assets.red-gate.com/community/books/sql-code-smells.pdf, last accessed 2019/12/29 (Storing the hierarchy structure in the same table as the entities that make up the hierarchy)
Dintyala, P., Narechania, A., Arulraj, J.: SQLCheck: automated detection and diagnosis of SQL anti-patterns. In: 2020 ACM SIGMOD International Conference on Management of Data, pp. 2331–2345. (2020). https://doi.org/10.1145/3318464.3389754 (Adjacency List)

The list of all the queries