PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 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
901 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
902 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
903 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
904 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
905 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
906 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
907 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
908 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
909 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
910 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
911 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
912 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
913 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
914 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
915 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
916 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
917 Update prevention may prevent legal updates Find triggers that try prevent updating data in a certain column but prevent also certain legal updates - updates that write to a field a value that was in the field before the update. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
918 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
919 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
920 Updating or deleting data in a routine without restricting rows This query identifies user-defined routines that contain unbounded Data Modification Language (DML) statements. Specifically, it flags routines containing UPDATE or DELETE operations that lack a qualifying WHERE clause. Such statements result in full-table modifications, affecting every row in the target relation. While valid in specific maintenance contexts, this pattern typically represents a critical logic error in transactional code, posing a severe risk of unintended massive data loss or corruption. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-20 18:27 MIT License View