| 921 |
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 |
| 922 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 923 |
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 |
| 924 |
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 |
| 925 |
Useless type indication |
Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 926 |
Useless type indication (2) |
Find columns and parameters where the type of the identifier is perhaps explicitly indicated in the name. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 927 |
User-defined routine execution privilege has been granted to PUBLIC |
You should follow the principle of least privilege and thus not have in your database user-defined routines that execution privilege is granted to PUBLIC, i.e., to all the database users now and in the future. By default, PostgreSQL gives routine execution privileges to PUBLIC. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 928 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 929 |
User-defined routines that use dynamic SQL to execute data manipulation statements |
Find user-defined routines that use dynamic SQL to execute data manipulation statements (SELECT, INSERT, UPDATE, DELETE). |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 930 |
User-defined routines that use keyword DECLARE but do not declare anything |
Find user-defined routines that use keyword DECLARE but do not declare anything. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 931 |
User-defined routines that use md5 hash for other purposes than generating test data |
Find user-defined routines that use md5 hashes for the security purposes. Nowadays such hashes can be calculated too quickly and its use should be avoided at least for hashing passwords. Exclude routines that invoke both md5 function and generate_series function and are thus probably used to generate test data. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 932 |
User-defined routines that use positional references to parameters |
Use parameter names instead of positional references to improve code evolvability. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 933 |
User-defined routines with dynamic SQL that are potential targets of the SQL injection attack |
Find routines that have at least one input parameter, use dynamic SQL but do not escape the input arguments at all. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 934 |
Username is not unique |
Find textual columns that potentially contain usernames (including columns that potentially contain e-mail addresses) that do not have a unique constraint or a unique index that involves only this column. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 935 |
Using AFTER triggers to enforce constraints |
Do not let the system to do extra work. Checking a constraint with an AFTER trigger means that the trigger procedure will be executed after the data modification and if the check fails, then the system has to do extra work to roll back the changes. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 936 |
Using BEFORE triggers to log data changes |
Do not let the system to do extra work. Logging changes with a BEFORE trigger means extra work for rolling back the changes in case the logged data modification fails. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 937 |
Using conditionals to determine the returned value |
Use SQL language instead of PL/pgSQL where possible. Instead of using an IF statement, you can check as to whether the data modification succeeded or not by using the RETURNING clause in the data modification statement. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 938 |
Using in some way reserved (in PostgreSQL) SQL keywords as the names of a database object (aggregate view) |
"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should not cause confusion. Find the distinct names (identifiers) of user-defined objects that are SQL keywords that are not completely unreserved in PostgreSQL, i.e., these either never cannot be used as regular identifiers or cannot be used in case of some type of database objects. In PostgreSQL "there are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. " (PostgreSQL manual) Moreover, such identifiers are often too general, i.e., do not provide enough information about the named object. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 939 |
Using in some way reserved (in PostgreSQL) SQL keywords as the names of a database object (detailed view) |
"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should not cause confusion. Find the names (identifiers) of user-defined objects that are SQL keywords that are not completely unreserved in PostgreSQL, i.e., these either never cannot be used as regular identifiers or cannot be used in case of some type of database objects. In PostgreSQL "there are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. " (PostgreSQL manual) Moreover, such identifiers are often too general, i.e., do not provide enough information about the named object. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 940 |
Using routine name in front of a parameter name in a routine body to refer to the parameter of the routine |
Write code that is easy to understand and not unnecessarily long. A routine cannot have two or more parameters with the same name. In this case using longer identifier in the form routine_name.parameter name is unnecessary. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |