PostgreSQL Query Catalog

Find non-foreign key base table columns that name refers to the possibility that these are used to register e-mail addresses. Find the columns that do not have any simple CHECK constraint that contains @ sign. A simple check constraint covers a single column. In this case registration of e-mail addresses without @ is most probably not prohibited.

Find non-foreign key base table columns that name refers to the possibility that these are used to register prices/quantities. Find the columns that do not have any simple CHECK constraints, i.e., a constraint that covers only this column. In this case registration of negative price/quantity is most probably not prohibited.

Find base tables columns with temporal types (date and timestamp) that do not belong to a foreign key and that do not have any associated simple CHECK constraints, i.e., constraint that involves only one column. For instance, in the column registration_time that does not have any associated CHECK constraints could be values '1200-01-01 00:00' or '5900-12-31 00:00'. Rows with these values most probably represent wrong propositions and the system should restrict registration of such data.

Find non-foreign key base table columns that name refers to the possibility that these are used to register URLs. Find the columns that do not have any simple CHECK constraint that references to a protocol. A simple check constraint covers a single column. In this case registration of URLs without a protocol is most probably not prohibited.

Find all base table columns in case of which the system can use TOAST technique.

Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed so that it is different than the default strategy determined by the type. Make sure that the new strategy is optimal.

Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed to plain. It means that potentially, if a value in the column is large enough, it is not possible to save the row.

Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table.

Find base table columns with an array type. Think through as to whether a column with an array type could be replaced with a separate table.

Find base table columns with CHAR(n) or VARCHAR(n) type and make sure that n is not too big or too small. Also make sure that you do not use CHAR(n) in case of columns that have to keep variable length strings. "Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way" (https://www.postgresql.org/docs/current/datatype-character.html)

Find base table columns with CITEXT type and make sure that case insensitivity is really needed in case of this column.

Find base table columns with DECIMAL (p, s) or NUMERIC (p, s) type and make sure that precision p and scale s are not too big or too small.

Each column should have the most appropriate data type. Developers sometimes misuse BIGINT, INTEGER, TEXT or VARCHAR type in places where a type that permits smaller values would be more appropriate and semantically descriptive.

Each column should have the most appropriate data type. Developers sometimes forget to use SMALLINT type even if it is logically the best choice. Developers also sometimes forget to use BOOLEAN type and instead invent something.

Find base table columns that have the same name and type but different field size.

The choice of data types should reveal as much as possible about the nature of the data in the column. The type of these columns could be CHAR(1) and they should have a constraint that a value in the column cannot be an empty string.

Each column should have the most appropriate data type. If one does not want to have in a table the surrogate key that values are generated by using a sequence generator, then one may use instead a column with Universally Unique Identifiers as the key column.

Identify possibly missing CHECK constraints.

What are the base tables and foreign tables without any associated (directly or through domains) check constraints? A NOT NULL constraint is a kind of CHECK constraint. However, this query does not take into account NOT NULL constraints.

Find base tables and materialized views that do not have any index.