PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 20 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 Columns with only one value Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
2 Declaratively partitioned tables without partitions Find declaratively partitioned tables that do not have any partitions. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
3 Disabled rules Identify disabled rules. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
4 Disabled system triggers (i.e., disabled enforcement of constraints) These triggers should be enabled because otherwise some important functionality regarding constraints like enforcing referential integrity does not work. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
5 Disabled user triggers Identify disabled triggers. These should be enabled or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
6 Empty columns Find columns in non-empty tables that do not contain any values. If there are no values in a columns, then it may mean that one hasn't tested constraints that have been declared to the column or implemented by using triggers. It could also mean that such columns are not needed at all. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
7 Empty schemas This query identifies empty schemas within the database. A schema is considered empty if it exists as a namespace but contains no database objects, such as tables, views, functions, or types. The presence of such schemas often indicates artifacts from failed or incomplete migrations, obsolete application components, or setup errors, and they can be safely removed to reduce schema clutter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:15 MIT License View
8 Empty tables Find base tables where the number of rows is zero. If there are no rows in a table, then it may mean that one hasn't tested constraints that have been declared to the table or implemented by using triggers. It could also mean that the table is not needed because there is no data that should be registered in the table. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
9 Potentially unused sequence generators Find sequence generators that are not associated with any column through the default value mechanism. Please note, that it is also possible to refer to a sequence generator from a routine or from an application. If these are indeed not used, then these should be dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
10 Unused composite types (for table columns, typed tables, input and output parameters) Find user-defined composite types that are not used in case of any table, column, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
11 Unused domains (for base table columns and parameters) Find domains that are not used in case of any base table column and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
12 Unused enumerated types (for base table columns, domains, and parameters) Find enumerated types that are not used in case of any base table column, domain, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
13 Unused foreign data wrappers Find foreign data wrappers that do not have any associated foreign servers. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
14 Unused foreign servers Find foreign servers that do not have any associated foreign tables. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
15 Unused named input parameters Find named input parameters that are not referenced in the routine body. All the parameters that are presented in the routine signature declaration must be used in its body. Otherwise these are dead code elements. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
16 Unused schemas Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
17 Unused trigger functions Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
18 Very similar (but not equal) routine names This query audits the schema for semantic ambiguity by identifying pairs of routine names (functions, procedures) that exhibit high textual similarity but are not identical. It filters for name pairs with a Levenshtein edit distance of exactly one (less than two, but excluding equality). This specific filter targets typographical errors (e.g., calc_tax vs. calc_tux) or inconsistent singular/plural naming (e.g., get_user vs. get_users), while correctly ignoring valid method overloading where names are identical. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-25 17:29 MIT License View
19 Very similar domain names This query identifies potential redundancy or ambiguity in the schema by detecting pairs of user-defined domain names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters. Crucially, the query implements a filter to exclude pairs where the divergence is attributable solely to numerical digits. This heuristic prevents false positives for valid domain variations based on size or version (e.g., d_name_20 vs. d_name_50), focusing the analysis strictly on likely typographical errors or semantic duplicates. Problem detection INFORMATION_SCHEMA only 2025-12-13 12:07 MIT License View
20 Very similar table names This query identifies potential redundancy or ambiguity in the schema by detecting pairs of table names with high textual similarity. It utilizes the Levenshtein distance algorithm to find name pairs that differ by fewer than two characters (i.e., a distance of 0 or 1). This check applies across different types of tables (base tables, foreign tables, derived tables), helping to uncover typographical errors (e.g., users vs user), inconsistent pluralization, or confusingly named entities that violate the principle of distinct and descriptive identifiers. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-25 17:24 MIT License View