PostgreSQL Query Catalog

Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern).

Find views and materialized views that use rank and dense_rank window functions.

This query identifies potential null-propagation defects in user-defined routines and views. It targets subqueries utilizing the standard concatenation operator (||) where operands are not protected by a coalesce() function. In PostgreSQL, the operation string || NULL yields NULL, causing the entire result to vanish if any component is missing. This behavior is often unintentional. The query flags these risky patterns, suggesting remediation via explicit null handling or the adoption of null-safe alternatives like concat(), concat_ws(), or format().

This query identifies superfluous function calls within routines and views, specifically targeting invocations of coalesce(), concat(), or concat_ws() that are supplied with only a single argument. These functions are variadic and designed to operate on multiple values (e.g., returning the first non-null value or joining strings). When called with a single argument, they function as an identity operation, returning the input unchanged. This pattern indicates either a coding error (missing arguments) or redundant logic that should be removed to simplify the expression.

This query identifies performance inefficiencies within user-defined routines caused by a mismatch between the logic in the routine and the definition of function-based indexes. PostgreSQL's query optimizer requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but is accessed in a routine using a different function (e.g., lower(col)). Consequently, the optimizer fails to utilize the pre-calculated index, forcing a costly sequential scan.

Find table columns with timestamp/date types that data type and dynamically found default value have a different type.

Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp.

This query identifies user-defined routines and derived tables (views or materialized views) that use the COUNT(*) aggregate function (without a FILTER clause) alongside an OUTER JOIN and a GROUP BY clause. This combination is a common SQL anti-pattern. When grouping with an outer join, COUNT(*) counts the rows themselves, including the NULL-filled rows generated for non-matching records. Consequently, it inaccurately reports a count of 1 for completely empty groups instead of the correct count of 0. To get accurate results, developers should count a specific non-null column from the joined table.

This query identifies CHECK constraints that use non-deterministic time functions (such as now or current_date) in a way that causes initially valid data to become invalid over time. For example, a constraint like localtimestamp(0) > end_date is an anti-pattern because advancing time will eventually violate it. However, safe implementations—such as birth_date < CURRENT_DATE—are permitted, because once the condition is met, it remains true indefinitely as time moves forward.

This query detects internal inconsistency within individual database objects (user-defined routines, views, materialized views). It flags objects that utilize both the standard concatenation operator (||) and variadic concatenation functions (concat() or concat_ws()) within the same definition body. Mixing null-unsafe operators (||) with null-safe functions (concat) in a single routine suggests a lack of coherent logic or an incomplete refactoring effort, potentially leading to confusing behavior regarding NULL handling.

Find user-defined routines that contain an invocation of a system-defined function without providing any argument. The query considers all aggregate functions as well as some popular scalar functions.

Find base table columns and domains that have a textual type but the default value that represents a temporal value (either a static value or invocation of a function that returns such value). Specify for each column/domain a right data type that takes into account expected values in the column/domain.

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.

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).

Avoid creating user-defined routines that have the same name as some system-defined routine because it may cause confusion.