PostgreSQL Query Catalog

Find base tables that have optional columns for recording both given name and surname and do not have a CHECK constraint that requires that at least one of the name components must be registered in case of each person.

Find base tables that have at least two columns that have Boolean type and have at least one Boolean column that is not covered by a CHECK constraint involving more than one Boolean column. The Boolean columns possibly mean that we want to record data about states. Often the states depend on each other. For instance, if an order is archived it must be inactive.

Find base tables that have at least two columns that have DATE or TIMESTAMP (with or without time zone) type and do not have any associated CHECK constraint that involves two or more of these columns. The columns mean that we want to record data about events or processes, which often have a certain order. Hence, in case of each row of such a table the values in these columns must be in a certain order. For instance, the end of a meeting cannot be earlier than the beginning of the meeting.

Find non-foreifgn key base table columns that have not been defined based on a domain but that have the same properties (data type, field size, default value, and pemisson to use NULLs) as some domain. If you define a domain, then you should try to use it in case of multiple columns.

Find as to whether in case of foreign key constraints both the compensating actions RESTRICT and NO ACTION are used within the same database. If the same thing has to do in different places, then try to do it in the same way.

Find table and domain CHECK constraints that compare the result of a Boolean expression with a Boolean value. If you can choose between two logically equivalent Boolean expressions choose the more simple expression.

This query identifies table columns and domains that are configured with a semantically void DEFAULT value. It specifically flags defaults that are an empty string ('') or a string consisting solely of whitespace characters (e.g., spaces, newlines). This practice is a design flaw because it automatically populates the database with non-substantive data, which can lead to application-level bugs when code does not explicitly check for such "blank" values in addition to NULL.

This query identifies a logical redundancy and likely data type misuse by finding CHAR columns that have a CHECK constraint on their value's length. The CHAR(n) data type is fixed-width and space-padded, meaning any non-NULL value will have a character length of exactly n. Therefore, a CHECK constraint on length is either superfluous (if it checks <= n) or will always fail (if it checks < n). This pattern indicates that the developer intended to store a variable-length string with a maximum length, for which the VARCHAR data type is the correct and more efficient choice.

This query identifies the semantic misuse of the CHAR(n) data type for non-foreign key columns where n > 1. It operates on a heuristic, flagging columns whose names suggest they store variable-length data (e.g., "name", "comment", "description", "email") rather than genuinely fixed-length data like standardized codes or hash values. Because CHAR(n) is a fixed-width, space-padded type, its use for variable-length strings is inefficient in terms of storage and can introduce application-level logic errors, making VARCHAR(n) the appropriate alternative.

Find base tables where more than one column gets the default value by using the sequence generator mechanism.

Find row level before insert and before update triggers that only task is not to raise an exception and where the variable NEW is not used in the trigger routine outside the RETURN clause.

Find row level before delete triggers that only task is not to raise an exception and where the variable OLD is not used in the trigger routine outside the RETURN clause.

Find regular expressions that name character classes a-zA-Z.

Enforce all the constraints. A binary relation is called irreflexive, if it does not relate any element to itself.

Find implementations of state machines that uses a set of one or more Boolean columns. These columns could have the type Boolean or could probably (based on the column name and non-participation in a foreign key) contain values that represent truth values.

Find implementations of state machines that uses a set of columns with a timestamp type.

Find the names of foreign key columns that are too generic. The expressive names of table columns allow database users better and more quickly understand the meaning of data in the database. A person could participate in a process or be associated with an object due to different reasons. Thus, foreign key column names like isik_id, person_id, tootaja_id, worker_id etc. are too generic. The name should refer (also) to the reason why the person is connected.

Find names (identifiers) of user-defined database objects that are 63 bytes long. This is the longest permitted length of identifiers if the default value of the NAMEDATALEN parameter has not been changed. PostgreSQL shortens too long identifiers automatically. Automatic code modification could break it somewhere.

A large routine may have multiple tasks that should be split between multiple routines, each of which has a more focused task. Find the PL/pgSQL routines where the number of physical lines of code is bigger than 40.

A large routine may have multiple tasks that should be split between multiple routines that have a more focused task. Find the SQL routines where the number of statements (logical lines of code) is bigger than 5.