| 1 |
Case insensitive search |
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). |
General |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:55 |
MIT License |
View |
| 2 |
Coalesce/Concat need at least two arguments |
Find user-defined routines and derived tables (views/materialized views) that have a subquery that contain an invocation of Coalesce or Concat function with just one argument. You should use the Coalesce and Concat functions correctly by invoking these with at least two arguments. If one invokes these functions with one argument, then the functions will return the input value. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-12-08 11:49 |
MIT License |
View |
| 3 |
Derived tables with ranking |
Find views and materialized views that use rank and dense_rank window functions. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2020-11-06 14:51 |
MIT License |
View |
| 4 |
Function in a function-based index of a column is different of the function that is used in the query in a routine based on the column |
Create appropriate indexes to speed up queries. If you apply a function to a column in a query, then create a function-based index based on the function to the column. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-04 17:26 |
MIT License |
View |
| 5 |
Inconsistency between the type and the default value of a column (date and timestamp values) |
Find table columns with timestamp/date types that data type and dynamically found default value have a different type. |
Problem detection |
INFORMATION_SCHEMA only |
2021-02-25 17:29 |
MIT License |
View |
| 6 |
Incorrect reference to a system-defined function in the routine body |
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. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:48 |
MIT License |
View |
| 7 |
Incorrect use of non-deterministic functions in CHECK constraints |
Do not create a constraint in case of which data that satisfied a constraint c at the registration time suddenly does not satisfy it any more as the time goes by. Find all the check constraints that use non-deterministic functions (now, current_timestamp, localtimestamp, current_date, current_time) in a way that makes this situation possible. Fort instance, localtimestamp(0)>end_date is an example of such constraint. |
Problem detection |
INFORMATION_SCHEMA only |
2024-11-22 15:29 |
MIT License |
View |
| 8 |
Invocation of a system-defined routine without providing any arguments |
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. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2023-12-22 13:00 |
MIT License |
View |
| 9 |
Mixing Concat and || |
Find cases where different means are used to concatenate text within the same object. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-12-03 14:36 |
MIT License |
View |
| 10 |
Mixing Concat and Coalesce |
Find cases where different means are used to deal with NULLs in case of concatenating texsts. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-12-08 14:25 |
MIT License |
View |
| 11 |
Perahaps Coalesce invocation is missing or Concat should be used |
Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes || operator but does not use Coalesce function to ensure that the arguments are not NULL. In PostgreSQL expression value || NULL returns NULL. In order to get value as the result, one has to replace NULL with a value (empty string) by using, for instance, Coalesce function (an alternative is to use a CASE expression). Instead of || + Coalesce, one could use Concat, Concat_ws, or Format functions for the concatenation. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-12-08 11:51 |
MIT License |
View |
| 12 |
Perhaps Count(*) is wrongly used |
Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes Count aggregate function like this - Count(*), uses outer join, and grouping. In case of grouping you do not want to get an answer that an empty group contains one member. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-10-25 16:40 |
MIT License |
View |
| 13 |
Perhaps the type of a base table column/domain should be temporal (based on default values) |
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. |
Problem detection |
INFORMATION_SCHEMA only |
2021-02-25 17:30 |
MIT License |
View |
| 14 |
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 |
2021-02-25 17:29 |
MIT License |
View |
| 15 |
User-defined routines that have the same name as some system-defined routine. |
Avoid creating user-defined routines that have the same name as some system-defined routine because it may cause confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |