PostgreSQL Query Catalog

This query identifies SQL routines that implement optimistic concurrency control via the xmin system column but fail to provide an execution status to the invoker. Specifically, it flags functions that perform UPDATE or DELETE operations filtered by xmin (a version check) but do not return information regarding the operation's success (e.g., a row count or a BOOLEAN status). This is a critical logic flaw; if a concurrency conflict occurs (the row was modified by another transaction), the operation yields zero rows. Without a return value, the failure is silent, leaving the calling application unaware that the data modification did not occur.

This query identifies columns that are both NULLable and have a DEFAULT value. This configuration represents a semantic contradiction: the DEFAULT clause implies that a value should always exist for the column, while the absence of a NOT NULL constraint explicitly permits the absence of a value. The presence of a DEFAULT strongly suggests the column's business logic requires a value, and therefore it should be defined with a NOT NULL constraint to enforce this consistently and make the schema's intent unambiguous.

Find optional base table columns that participate in a UNIQUE constraint or index. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns.

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

Find optional composite foreign keys that do not have MATCH FULL specified. Without MATCH FULL the system will permit partial foreign key values

Find optional base table columns that participate in a UNIQUE constraint or index but do not participate in a foreign key constraint. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns.

Find non-function based indexes (both unique and non-unique) that duplicate each other because their first column is identical and the operator class that is used in case of the first column is identical. Include unique indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration.

Routines in the same schema that have the same name and that have parameters with different types or different number of parameters are not considered to be in conflict at the creation time. However, if defaults are provided in the definition of parameters, then these routines might be conflict during runtime.

This query identifies potentially overly strict CHECK constraints applied to base or foreign table columns that store personal names. It highlights validation rules that might inadvertently block valid legal names.

This query identifies classifier tables where the code column uses a data type that accommodates unnecessarily large values for reference data. Specifically, it flags code columns defined as integer, bigint, or varchar(n) where n > 10.

What are the pairs of base tables that have at least two columns with the same names and data types. The tables might violate the principle of orthogonal design and hence might facilitate uncontrolled data redundancy over different tables.

Find non-key base table columns with the same name and type that have different default values. Be consistent. Columns with the same name and type shouldn't probably have different default values in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)

Find non-key base table columns with the same name and type that have in some cases a default value and some cases not. Be consistent. Columns with the same name and type should probably either always have a default value in case of different tables or never have a default value in case of different tables. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code)

Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.

If the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code.

If the name of a routine parameter and the name of a column of a table that is used in the routine are the same, then it makes it more difficult to understand the code.

Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.

Find columns that potentially contains passwords and that participate in a unique constraint or index

Find base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database.

Find the names of database objects where 0 sign is perhaps used instead of o.