PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 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
201 Too short domain constraint names Find names of domain constraints that are shorter than the length of the name of the domain + two characters. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
202 Too short view names Names should be expressive. Find views that name is shorter than the average length of the the names of its directly underlying tables (both base tables and derived tables). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
203 Unnecessary usage of the numeric type in case of base table columns Find base table columns that have type NUMERIC and the scale is 0, i.e., one can record in the column only integer values. Arithmetic operations are slower in case of the numeric type compared to an integer type. Thus, in order to record integer values, one should use columns with the type SMALLINT, INTEGER, or BIGINT instead of NUMERIC(p,0). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
204 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
205 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
206 Updatable foreign tables that refer to another PostgreSQL table This query identifies foreign tables established via the postgres_fdw (PostgreSQL Foreign Data Wrapper) that are configured to permit data modification (updatability). While postgres_fdw supports INSERT, UPDATE, and DELETE operations on remote tables, enabling this capability introduces complexity regarding distributed transactions, performance, and security. The query serves as an audit tool to verify that the updatability of these foreign tables is a deliberate architectural requirement and not an unintended default configuration. General INFORMATION_SCHEMA only 2025-11-20 11:45 MIT License View
207 Updatable views missing WITH CHECK OPTION This query identifies automatically updatable views that lack the WITH CHECK OPTION clause. Without this constraint, it is possible to perform INSERT or UPDATE operations through the view that create rows which do not satisfy the view's defining predicate (the WHERE clause). This results in "phantom" modifications where the new or updated data is successfully committed to the base table but is immediately excluded from the view's result set. Enforcing WITH CHECK OPTION ensures that all data modifications performed through the view remain visible within the view. Problem detection INFORMATION_SCHEMA only 2025-12-26 09:54 MIT License View
208 Updatable views with WHERE clause that do not have WITH CHECK OPTION constraint This query identifies automatically updatable views that define a row restriction (via a WHERE clause) but lack the WITH CHECK OPTION constraint. In the absence of this constraint, it is possible to perform INSERT or UPDATE operations through the view that result in rows satisfying the base table constraints but failing the view's inclusion criteria. This leads to "phantom updates," where the modified data is committed to the database but immediately disappears from the view's scope. Enforcing WITH CHECK OPTION ensures that all modifications performed through the view respect its defining predicate. Problem detection INFORMATION_SCHEMA only 2026-01-19 14:02 MIT License View
209 UPDATE triggers Find all UPDATE triggers. Make sure that they specify a correct set of columns in which data modification will fire the trigger. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
210 UPDATE triggers where updated columns have not been specified (the trigger could executed too often) Find UPDATE triggers where updated columns are not specified. These triggers could be executed too often because unneeded executions are not prevented. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
211 UPDATE triggers where WHEN clause has not been specified (the trigger could executed too often) Find UPDATE triggers where WHEN clause is not specified. These triggers could be executed too often because unneeded executions are not prevented. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
212 Use invocation of a precise function instead of casting in a default value expression Be precise and write as little code as possible. Prefer expressions with simple invocations of functions like localtimestamp, current_timestamp, and current_date over expressions like (now())::date. Find table columns that have a default value that casts the type of the returned value of a non-deterministic function (now, localtimestamp, current_timestamp, and current_date). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
213 Using an internal data type - name Find base table columns that use type name that is used in system catalog tables. It is not a problem if the column is meant for recording identifiers of database objects. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
214 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
215 Views with the WITH LOCAL CHECK OPTION constraint Find updatable views that have WITH LOCAL CHECK OPTION constraint. The predicate of a view is the conjunction of the predicates of its (directly and indirectly) underlying tables (both base tables and derived tables) as well as the predicate of the view itself. In case of using WITH LOCAL CHECK OPTION constraint "New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION)." (PostgreSQL manual) Thus, use instead WITH CASCADED CHECK option to instruct the system to check new rows against the entire predicate of the view. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
216 A getter does not return a value Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "get"). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
217 A large number of triggers Show user-defined triggers if there are more than 9 different trigger routine bodies, i.e., different triggers on different tables that do the same thing count as one trigger. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
218 All covering indexes Find all covering indexes, which include data from additional columns in leaf blocks. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
219 All declaratively partitioned tables Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
220 All derived tables that use joining tables This query identifies complex derived tables (views and materialized views) that perform data integration operations. Specifically, it filters for views whose definition involves joining two or more distinct tables. This distinguishes non-trivial views—which encapsulate relationship logic and data aggregation—from simple projection views that merely mirror a single base table. The result highlights the core reporting and data integration layer of the schema. General INFORMATION_SCHEMA+system catalog base tables 2025-12-26 10:02 MIT License View