| 1001 |
Do not leave out the referential constraints (based on column names) (2) |
Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, but have a name that reffers to the possibility that these are used to record references to a user. Exclude columns that have the default value CURRENT_USER or SESSION_USER. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1002 |
Do not leave out the referential constraints (based on column names) |
Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, do not have an associated sequence generator, but have a name that reffers to the possibility that these are used to record some kind of codes or id's. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1003 |
Do not leave out referential constraints (based on composite keys) |
Try to find missing foreign key constraints. Find columns of base tables that are not covered by any foreign key constraint but belong to a composite key, do not have an associated sequence generator, and have a name that refers to the possibility that these are used to record some kind of codes or id's. Moreover, there must be at least one other base table that has a column with the same name. Such strategy would find missing constraints in tables that implement many-to-many relationship types but which that are not complete "islands" in terms of missing foreign key constraints. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1004 |
Do not leave out the referential constraints (based on adjacency list design) |
Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1005 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1006 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1007 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1008 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1009 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1010 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1011 |
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-11-07 10:11 |
MIT License |
View |
| 1012 |
Columns that have the same name as some domain/type |
Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1013 |
Domain name and type name are the same |
Use different names to avoid confusion. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 1014 |
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 |
| 1015 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1016 |
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 |
| 1017 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1018 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1019 |
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 |
2025-11-07 10:11 |
MIT License |
View |
| 1020 |
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 |
2025-11-07 10:11 |
MIT License |
View |