PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1050 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 The proportion of mandatory and optional textual base table columns Find the number of textual base table columns, the number of optional textual base table columns (permit NULLs), and the number of mandatory textual base table columns (do not permit NULLs). Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
202 The proportion of using different integer types as types of base table columns Find the number of base table columns that use different integer types (SMALLINT, INTEGER, BIGINT) and their proportion from the overall set of columns that use an integer type. Sofware measure INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
203 The same sequence generator is used in case of multiple columns Do not cause a potential performance bottleneck by having a shared resource. By having a shared sequence it is not possible to change properties of sequences of different tables independently (for instance the owner column or step), i.e., it increases coupling between tables. By having a shared sequence it is impossible to specify the owner (table column) to the sequence generator. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
204 The same trigger function is used in case of multiple tables Find trigger functions that are used in case of more than one table. Although it is legal, one must be careful when changing the functions in order to avoid unwanted consequences. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
205 Three-valued logic (Boolean columns) Find base table columns that have Boolean type and do not have NOT NULL constraint. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
206 Too generic names (domain constraints) Find domain CHECK constraints that have a too generic name - for instance, the name contains word "data" ) or the name is an abbreviation of the constraint type name (for instance, "chk" or "chk1"). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
207 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
208 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
209 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
210 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
211 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
212 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
213 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
214 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
215 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
216 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
217 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
218 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
219 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
220 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