PostgreSQL Query Catalog

This query identifies columns in base and foreign tables that are intended to store age values. It detects these fields by analyzing both their naming conventions and data types.

Find all CHECK constraints (except NOT NULL) of domains that are not associated with any column.

Find all the default values of base table, view, and foreign table columns.

Find all columns that have a dynamic default value, i.e., the value is returned by a function but the function is not for invoking a sequence.

Find base tables where all the columns have a default value.

Find all columns that have a static default value, i.e., the value is not returned by a function.

Find domains that specify a default values and columns that are defined based on the domain. Make sure that there are no unsuitable default values.

Find all domains.

Find all foreign tables.

Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications).

Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements.

Find all the schemas in the database that are not used for the system purposes.

Find base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these.

Find base table columns that name refers to the possibility that these are used to register comments/descriptions. Find the columns where the data type is not VARCHAR and TEXT or in case of VARCHAR the maximum number of permitted characters is smaller than 1000. In case of determining field sizes choose a size that permits registration of all possible legal values.

This query identifies a semantic mismatch in data type selection for columns intended to store measurement data. It targets columns whose names imply a quantitative measurement (e.g., "length", "weight", "count", excluding boolean prefixes like "is_") but are not defined with a numeric data type (INTEGER, NUMERIC, etc.). Storing measurements as text (VARCHAR) prevents mathematical operations, aggregation, and proper sorting, and is considered a design flaw.

Find non-textual base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). The codes can contain additional symbols to numbers.

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 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 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 data about prices/sums of money. Find the columns that have decimal type but have a too big (bigger than six) or a too small scale (zero). The selection of field size must be precise and should take into account the possible data in the column.