PostgreSQL Query Catalog

Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator.

Find table functions that do not have any parameters. Prefer simpler and more portable solutions.

Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:].

Find base table columns that name refers to the possibility that these are used to register passwords. Return a value from each such column. Make sure that the password is not registered as open text.

Find user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases.

Find user-defined routines that are invoked by exactly one user-defined routine.

Find user-defined non-trigger SQL and PL/pgSQL routines that name starts with "set" (but not with "setting") but do not contain a UPDATE statement.

Find implementations of state machines that uses an enumeration type.

Find implementations of state machines that use a state classifier table.

Find table names that are the same as some routine name. Use different names to avoid confusion.

Find base tables and foreign tables where based on a check constraint, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a check constraint permits only one possible value in a column, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.

Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint.

Find base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these.

Find base table columns that name refers to the possibility that these are used to register comments/descriptions. Find the columns where the data type is not VARCHAR and TEXT or in case of VARCHAR the maximum number of permitted characters is smaller than 1000. In case of determining field sizes choose a size that permits registration of all possible legal values.

Find non-textual base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). The codes can contain additional symbols to numbers.

Find base table columns that name refers to the possibility that these are used to register personal names. Although there are very long personal names the general approach is to register a shortened version of these. Thus, a large field size is not a good idea because it would cause usability and security problems. There are persons who only have one name component (mononymous persons). Database design must take it into account.

Find base table columns that name refers to the possibility that these are used to register quantities of things. Find the columns that do not have a numeric type.

Find base table columns that name refers to the possibility that these are used to register prices/sums of money. Find columns that do not have a numeric type.

Find base table columns that name refers to the possibility that these are used to register data about prices/sums of money. Find the columns that have decimal type but have a too big (bigger than six) or a too small scale (zero). The selection of field size must be precise and should take into account the possible data in the column.

Find base table columns that belong to a primary key, unique, or foreign key constraint and that name refers to the possibility that these are used to hold surrogate key values. Find the columns where the data type of the column is not an integer type or uuid.