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
781 Unnamed parameters Find unnamed parameters in PL/PGSQL routines that do not declare aliases for parameters and in SQL routines. Avoid unnamed parameters because dependency on position in case of referencing the parameters makes evolving the code more difficult. In case of unnamed parameters - if one changes the order of parameters in the routine signature, then one has to change the body of the routine in order to use correct references. The bigger is the number of parameters in a routine the more the unnamed parameters make it more difficult to understand the routine. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
782 Unnecessary domains Domain is a reusable artifact. Effort of its creation should be paid off by the advantages that it offers. If a domain is used in case of at most one column of a base table or even if it is used in case of more than one column but it does not specify neither a default value nor a check constraint, then there is no point of creating the domain. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
783 Unnecessary explicit locking in read-only routines This query identifies read-only routines that unnecessarily utilize explicit locking mechanisms. PostgreSQL's Multi-Version Concurrency Control (MVCC) ensures that readers do not block writers (and vice versa) for standard query operations. Therefore, routines that perform no data modification (DML) and do not raise exceptions have no functional need to acquire table-level (LOCK TABLE) or row-level (FOR SHARE/UPDATE) locks. Using them in this context provides no benefit while actively degrading system concurrency by blocking other transactions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-10 13:23 MIT License View
784 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
785 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
786 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
787 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
788 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
789 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
790 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
791 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
792 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
793 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
794 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
795 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
796 Usage of base tables Find for each derived table the list of base tables that are used by the derived table. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
797 Usage of the non-standard now() function This query identifies all expressions that use the non-standard now() function. In PostgreSQL, now() is a historical, non-standard alias for the SQL-standard current_timestamp. While they are functionally identical within PostgreSQL (both returning the transaction start timestamp as a TIMESTAMPTZ), the use of current_timestamp is strongly preferred for reasons of code portability and adherence to standards. Standardizing on current_timestamp ensures the code is universally understood and easier to maintain or migrate to other database systems. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 10:01 MIT License View
798 Used indexes Find indexes that are used by the DBMS. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
799 Useless trivial non-trigger functions This query identifies user-defined routines (excluding triggers) that are functionally trivial. It flags routines whose body consists solely of returning a static value: either an input argument (identity function), a constant literal, or NULL. Such routines typically perform no computation, data manipulation, or side effects. They are likely placeholders, deprecated logic, or artifacts of incomplete refactoring, and should be reviewed for removal or implementation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 11:44 MIT License View
800 Useless trivial trigger functions This query identifies trigger functions that are functionally trivial, specifically those whose sole action is to execute RETURN NEW. In a BEFORE trigger context, this operation simply allows the data modification to proceed unchanged. If the function contains no other logic (e.g., validation, modification of NEW, or side effects), it performs no useful work and incurs unnecessary execution overhead. Such triggers are likely incomplete placeholders or obsolete code that should be removed. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 11:50 MIT License View