PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 7 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name ▲ Goal Type Data source Last update License
1 A state machine is implemented with the help of a state classifier table Find implementations of state machines that use a state classifier table. General system catalog base tables only 2025-11-07 10:11 MIT License View
2 Coupling of distinct entity lifecycles via shared state classifiers This query identifies a potential domain modeling flaw where multiple distinct entity tables reference a single, shared state classifier table. According to robust design principles, each main entity type should define its own independent state machine and lifecycle. Sharing a classifier creates undesirable coupling; even if the state vocabularies (e.g., 'Active', 'Inactive') appear identical currently, the business logic for distinct entities is likely to diverge over time. Furthermore, reliance on a universal state table often indicates an under-analyzed domain model utilizing overly generic state transitions. Problem detection system catalog base tables only 2026-01-19 17:36 MIT License View
3 Do not leave out the referential constraints (based on classifiers) Find non-key and non-foreign columns of base tables with a textual column and small field size in case of which there is a table with the name that is similar to the column name. Perhaps the table is a classifier table and the column should have a foreign key constraint referencing to the table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
4 Find columns that require new lookup tables (based on names and field sizes) This query detects data attributes that function as implicit classifiers but lack a corresponding reference entity. It targets textual columns that are not currently constrained (no PK, UK, or FK) but exhibit characteristics of coded data: they either have a very short length (≤ 3 characters) or possess identifiers typical of classifiers (e.g., 'status', 'type'). To reduce false positives, it excludes obvious free-text fields (names, comments) and verifies that no table with a similar name currently exists. This suggests the need to extract these attributes into a new dedicated reference table to enforce domain integrity. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-30 09:14 MIT License View
5 One true lookup table Find tables that contain all (or most) of the classifier values and tables that refer to these. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
6 Potentially a classifier is missing (based on field sizes) Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
7 Sequence generators not needed Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View