| 501 | Unnecessary usage privileges of PUBLIC | PostgreSQL gives by default some privileges to all the present and future database users (PUBLIC). Find usage privileges of collations, domains, foreign data wrappers, foreign servers, and sequences that have been given to PUBLIC. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-31 13:53 | MIT License | |
| 502 | Trigger routines with TG_OP variable that are not associated with a suitable trigger | Automatically defined TG_OP variable in a trigger function has data type text. Its value is a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired. Find the routines that according to the TG_OP value must react to a certain operation but the routine is not associated with any triggers that are fired by the operation. For instance, the routine specifies reaction to DELETE operation but the routine is not associated with any DELETE trigger. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-25 01:26 | MIT License | |
| 503 | STATEMENT level triggers that refer to the values of row variables NEW or OLD | Find STATEMENT level triggers that refer to the values of row variables NEW or OLD. NEW and OLD are special variables that can only be used in row-level trigger procedures. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 14:23 | MIT License | |
| 504 | Overloading | Make sure that there is genuine overloading instead of duplication or dead code. "In some programming languages, function overloading or method overloading is the ability to create multiple functions of the same name with different implementations." (Wikipedia) In PostgreSQL one can do it automagically by having multiple routines with the same name but different parameters in the same schema. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 11:40 | MIT License | |
| 505 | Perhaps duplicate check of empty strings (ver 2) | Find columns that have a check that prevents the empty string in the column but there is already another check on the column that enforces the constraint. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 11:30 | MIT License | |
| 506 | Permitting in a column only empty strings and strings that consist of whitespace characters | Find table CHECK constraints that permit in a column only empty strings or strings that consist of only whitespace characters. | Problem detection | INFORMATION_SCHEMA only | 2021-12-19 15:10 | MIT License | |
| 507 | Reasonable upper bound to the length of textual values is missing | Find non-foreign key base table columns that are not used to record comments/descriptions/explanations etc. and that have TEXT or VARCHAR type without restrictions to the field size (field size in case of VARCHAR or a CHECK constraint). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-16 12:32 | MIT License | |
| 508 | PL/pgSQL functions with consecutive RETURN clauses | Find PL/pgSQL functions with consecutive RETURN clauses. Only the first RETURN will be used, others are unnecessary. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-16 11:38 | MIT License | |
| 509 | All exclude constraints | Find all exclude constraints. | General | system catalog base tables only | 2021-12-16 11:21 | MIT License | |
| 510 | Consistency of CHECK constraint name and content | Find all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent. | General | INFORMATION_SCHEMA only | 2021-12-10 13:21 | MIT License | |
| 511 | System-generated table constraint names (constraints that involve one column) | Find the names of database constraints that have been system-generated. Additional restrictions are that the constraints must involve only one column and are associated directly with a table (not through a domain). Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. | Problem detection | system catalog base tables only | 2021-12-10 12:47 | MIT License | |
| 512 | 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 | |
| 513 | 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 | |
| 514 | 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 | |
| 515 | 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 | |
| 516 | Cycles in relationships | Find as to whether foreign key constraints between tables form a cycle. For instance, if table C refers to table B, table B refers to table A, table A refers to table C and all the foreign key columns are mandatory, then one cannot add data to the tables. Is cycle value in the result should not contain value TRUE. One should check manually as to whether the foreign key columns are mandatory or optional. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-28 15:15 | MIT License | |
| 517 | Short cycle (columns) | Find cases where two candidate keys of the same table that are also foreign keys reference to each other. | Problem detection | system catalog base tables only | 2021-11-28 02:08 | MIT License | |
| 518 | All short cycles (tables) | Find pairs of tables that have both a foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-27 20:54 | MIT License | |
| 519 | Short cycles (tables) | Find pairs of tables that have both a mandatory (NOT NULL) and not defrerrable foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-27 20:52 | MIT License | |
| 520 | Perhaps unnecessary privileges to use the database | Find as to whether a database user (except postgres), who is not a superuser, has Create (C) or Temporary (T) privileges to use the database. | Problem detection | system catalog base tables only | 2021-11-23 20:42 | MIT License | |