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 2023-11-26 15:39 MIT License View
2 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 2021-03-18 11:14 MIT License View
3 Multiple tables share the same state classifier Find cases where multiple tables share the same state classifier. For each main entity type one should create a separate state classifier table. Even if the classifier values are the same in case of two entity types for now these may become different in the future. Having a shared state classifier table usually means very simplistic state machines (states active and inactive) that could point to the gaps in analysis. Problem detection system catalog base tables only 2023-12-30 15:51 MIT License View
4 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 2021-02-25 17:29 MIT License View
5 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 2021-03-10 13:07 MIT License View
6 Potentially a classifier table 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 and where the column name does not refer to names or comments. Return only data about these columns where there is no table with a similar name. Return data only about tables that could be referenced from more than one table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-18 20:08 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 2024-12-18 10:00 MIT License View