PostgreSQL Query Catalog

Find cases where multiple columns with the same domain have exactly the same CHECK constraint that is directly associated with the table.

Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table.

Find cases where some CHECKS are associated with a domain and some with the base table columns that have the domain. Avoid duplication of code. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring CHECKS at the level of the domain and not at the level of base table columns.

Find cases where the list of valid data values in the column is specified in the column definition (in addition to specifying the type of the column) by using, for instance, check constraints or enumerated types. The check constraint is either associated directly with a table or is associated with a domain.

Find cases where the same column of a base table T is used to record references to multiple base tables. In addition, one has to add additional column to T for holding metadata about the parent table, referenced by the current row.

Find cases where the same table has multiple triggers with the same type (row-level, statement-level) that react to the same event with the same WHEN condition and with the same way (by invoking the same function).

Find cases where two candidate keys of the same table that are also foreign keys reference to each other.

Find cases where you store images and other media as files outside the database and store in the database only paths to the files.

Find cases where you store images and other media as files outside the database and store in the database only paths to the files.

Find check constraints of base table and foreign table columns that are either associated with more than one column and have at least one AND operation or are associated with exactly one column and have two or more AND operations.

Find CHECK constraints of base table and foreign table columns that are not valid. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint.

Find check constraints of base tables that cover exactly one column where the default value of the column is special value 'infinity' or '-infinity'. Find only such constraints that probably check a range of permitted values but do not consider that one of the values might be 'infinity' or '-infinity'. Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION.

Find check constraints of domains where the Boolean expression invokes an operation that does not match with the data type of the domain.

Find check constraints of tables that use a regular expression to check as to whether a registered string ends with an appropriate file extension. However, the expression does not put the dot sign into the square brackets nor does have the escape character \before it, i.e., it is interpreted as a single character not as the dot sign in the expression. In regular expressions the dot (.) matches any single character except the newline character.

Find CHECK constraints on base table or foreign table columns that contain data about e-mail addresses and apply unnecessary restrictions to the these, rejecting potentially some legal addresses. More precisely, find CHECK constraints that prevent registration of e-mail addresses with multiple @ signs.

Find CHECK constraints that cover at least one column and that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable.

Find CHECK constraints that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. CHECK(false) on a supertable is an appropriate CHECK constraint if one wants to prevent registering data directly to the supertable, i.e., data can only be added to the subtables.

Find check constraints that involve columns with the type Boolean.

Find CHECK constraints that refer only to mandatory columns but the Boolean expression has IS NULL condition.

Find CHECK constraints that use precise comparison (= or <>) with a regular expression or LIKE pattern.