| 961 |
Do not leave out the referential constraints (pairs of tables) |
Try to find missing foreign key constraints. Find pairs of base table columns that have the similar name, perhaps the same type, and that are not associated through a foreign key relationship. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:29 |
MIT License |
View |
| 962 |
Extensions that are available but are not installed |
Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. |
General |
system catalog base tables only |
2020-11-06 14:51 |
MIT License |
View |
| 963 |
Installed extensions |
Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database. |
General |
system catalog base tables only |
2020-11-06 14:51 |
MIT License |
View |
| 964 |
Routine body only in uppercase |
Uppercase means screaming and having code entirely in uppercase makes its reading more difficult. On the other hand, it would be a good idea to have keywords in uppercase. Find routines that body contains a SQL data manipulation statement (which shouldn't be entirely in uppercase) but still the body is completely in uppercase. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-04 20:12 |
MIT License |
View |
| 965 |
IS DISTINCT FROM should be used instead of <> in WHEN clauses |
Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. |
Problem detection |
INFORMATION_SCHEMA only |
2024-12-23 12:29 |
MIT License |
View |
| 966 |
Perhaps IS DISTINCT FROM should be used instead of <> in WHEN clauses |
Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. |
General |
INFORMATION_SCHEMA only |
2024-12-23 12:27 |
MIT License |
View |
| 967 |
Names of database objects that mix snake_case and camelCase/PascalCase |
Use consistent style of naming. Prefer snake_case. Regular identifiers are stored in the PostgreSQL system catalog in lowercase. Thus, if you use, for instance the identifier thisIsLongTableName, then, for instance,in the pg_dump result you will see the table name thisislongtablename. If the name in the system catalog is thisIsLongTableName, then it means that the name is a delimited identifier, i.e., case sensitive. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-05-05 07:07 |
MIT License |
View |
| 968 |
Columns that have the same name as some domain/type |
Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2022-11-28 14:47 |
MIT License |
View |
| 969 |
Domain name and type name are the same |
Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-02-25 17:30 |
MIT License |
View |
| 970 |
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 |
2021-11-05 13:18 |
MIT License |
View |
| 971 |
Privileges to use base tables |
Users (applications) should ideally use a database through virtual data layer and thus not directly use base tables. If there is a need to provide direct access to the base tables, then one should grant access based on the principle of least privilege, i.e., to the minimum possible number of base tables. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2020-12-29 10:38 |
MIT License |
View |
| 972 |
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 |
2021-11-04 11:52 |
MIT License |
View |
| 973 |
Mixing different mechanisms to generate surrogate values |
Use the same mechanism of generating surrogate key values throughout the database. The use of SERIAL notation/explicitly creating a sequence generator and declaration of a column as an identity column will cause the creation of an external and internal sequence generator, respectively. Nevertheless, one should try to stick with using one of the mechanisms in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) |
Problem detection |
INFORMATION_SCHEMA only |
2021-03-08 00:42 |
MIT License |
View |
| 974 |
There is no reason to use PL/pgSQL if you do not use one or more features of a procedural language |
Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs (variables, conditional statements, cycles, cursors, exception handling). |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-04 16:32 |
MIT License |
View |
| 975 |
There is no reason to use PL/pgSQL to write table functions |
Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs. You can create table functions by using SQL. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-04 11:28 |
MIT License |
View |
| 976 |
Base tables and foreign tables that have no CHECK constraints |
What are the base tables and foreign tables without any associated (directly or through domains) check constraints? A NOT NULL constraint is a kind of CHECK constraint. However, this query does not take into account NOT NULL constraints. |
Problem detection |
INFORMATION_SCHEMA only |
2021-02-25 17:29 |
MIT License |
View |
| 977 |
Pairs of base tables that have at least two columns with the same names and data types |
What are the pairs of base tables that have at least two columns with the same names and data types. The tables might violate the principle of orthogonal design and hence might facilitate uncontrolled data redundancy over different tables. |
Problem detection |
INFORMATION_SCHEMA only |
2022-11-09 13:13 |
MIT License |
View |
| 978 |
Percentage of optional columns in each base table |
What is the percentage of optional columns (that permit NULLs) in case of each base table? It is better to prohibit the use of NULLs in as many columns as possible. Otherwise the results of queries may be misleading. |
Sofware measure |
INFORMATION_SCHEMA only |
2020-11-08 20:55 |
MIT License |
View |
| 979 |
PL/pgSQL routines that use a cursor |
Working with sets of rows rather than processing each row separately is more effective. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2021-11-04 11:54 |
MIT License |
View |
| 980 |
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 |
2021-11-05 13:40 |
MIT License |
View |