PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1053 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
981 Unnecessary privileges to use trigger functions A user that corresponds to an application does not have to have privileges to use trigger functions. If it has these, then it violates the principle of least privilege. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
982 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
983 Unnecessary usage privileges of PUBLIC PostgreSQL gives by default some privileges to all the present and future database users (PUBLIC). Find usage privileges of collations, domains, foreign data wrappers, foreign servers, and sequences that have been given to PUBLIC. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
984 Unnecessary use of Coalesce This query identifies redundant null-handling logic within string manipulation expressions. It targets the use of explicit coalesce() calls nested inside functions that are already null-safe, such as concat(), concat_ws(), or format(). Since these functions implicitly treat NULL arguments as empty strings (or ignore them), wrapping arguments in coalesce(arg, '') is superfluous. The query deliberately excludes expressions using the standard concatenation operator (||), as coalesce() is legitimately required in that context to prevent null propagation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-03 15:58 MIT License View
985 Unnecessary use of gist index type in case of an exclude constraint Find exclude constraints that are based on the gist index type although the default b-tree index type would have been enough. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
986 Unsecure SECURITY DEFINER routines SECURITY DEFINER routines must be secured against the malicious use of pg_temp schema. Find routines that do not explicitly set the search path or do it incorrectly (the search path is between quotation marks) and are thus potential targets of the attack. pg_temp must be the last entry in search_path. The lack of search_path is allowed only if the SQL statements in routines explicitly refer to the schemas that contain the schema objects. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
987 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
988 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
989 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
990 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
991 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
992 Unused indexes Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
993 Unused indexes (2) Find indexes that are not used by the DBMS. Remember that indexes are not a "free lunch" and they slow down the processes of updating data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
994 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
995 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
996 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
997 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
998 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
999 Updatable views that have not been turned to read only This query identifies views that are automatically updatable by the database engine but lack explicit safeguards to prevent data modification. Specifically, it targets views that meet the criteria for auto-updatability (typically simple projections of a single base table) yet are missing an INSTEAD OF trigger or a DO INSTEAD NOTHING rule. Without these mechanisms, any INSERT, UPDATE, or DELETE operation performed against the view will seamlessly propagate to the underlying base table, which may violate the intended read-only design contract. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-16 15:31 MIT License View
1000 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