PostgreSQL Query Catalog

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 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 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 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 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 timestamps. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column type is "timestamp", then the suffix of the column name should be "aeg" (Estonian) or "time" (English).

Find base table columns that name refers to the possibility that these are used to register usernames, passwords, and salt. Password should not be open text. It should be a hash value that has been hashed by using salt.

Find base table columns that name refers to the possibility that these are used to register XML/JSON values. Find the columns that do not have an appropriate data type (xml, json, jsonb). One shouldn't use columns with a textual type to register such data.

Find base table columns that use type name that is used in system catalog tables. It is not a problem if the column is meant for recording identifiers of database objects.

Find base table columns with a check constraint that refers to a temporal function (current_timestamp, localtimestamp, current_date, or now) that return type is inconsistent with the data type of the column.

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 Boolean type that name refers to the possibility that these are used to register as to whether an entity is currently in active state or not. Find the base tables that have exactly one Boolean column. During the system design one should find all the possible states of an entity type that influence the behavior of the information system. Data as to whether an entity is in one of these states should be in the database. Having only two states - active/inactive - is sometimes a too big simplification.

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.

Find base table columns with the Boolean type that has a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves only this column. Avoid unnecessary constraints. It is quite improbable that there must be such constraints. For instance, a table with PRIMARY KEY () or UNIQUE () constraint can have at most two rows.

Find base table columns with the Money data type. Each value of the money type has associated currency sign that depends on server settings. It could be $. Moreover, using the values for arithmetic operations requires casts that makes the code more complicated.

Find base table columns with tsvector type in case of which it is not a generated column nor does the table has an associated trigger to calculate the tsvector value automatically.

Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value.