PostgreSQL Query Catalog

Find domain CHECK constraints that have a too generic name - for instance, the name contains word "data" ) or the name is an abbreviation of the constraint type name (for instance, "chk" or "chk1").

Find domains that are not used in case of any base table column and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

Find domains that declare a default value and that are used in case of multiple foreign key constraints that point to different tables. Domains should be used in a manner that does not cause unnecessary coupling of concerns. For instance, let us assume that columns client_state_type_code of table Client (that is used to implement the relationship with table Client_state_type) and worker_state_type_code of table Worker (that is used to implement the relationship with table Worker_state_type) have been defined based on the same domain. It the domain has a default value, then it determines the initial state of both clients and workers. However, it must be possible to determine the initial state independently in case of clients and workers.

Find domains that have been defined based on another domain. Do not specify domains based on existing domains. This would unnecessarily increase dependencies and complexity.

Find domains that have been used to define one or more base table non-foreign key columns and all the columns have the same default value that is associated directly with the column not with the domain. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring the default value at the level of the domain and not at the level of base table columns. An exception is when the domain is used to define foreign key columns. In this case, it would be appropriate to define the default value at the column level (because different foreign keys could have different default values).

Find domains that have the same properties (base type, character length, not null + check constraints, default value, collation). There should not be multiple domains that have the same properties. Do remember that the same task can be solved in SQL usually in multiple different ways. Therefore, the domains may have syntactically different check constraints that solve the same task. Thus, the exact copies are not the only possible duplication.

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 domains with NOT NULL constraints and base table columns that have been defined based on the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments.

Find duplicate constraints, which make it more difficult to maintain the constraints. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find duplicate foreign key constraints, which involve the same columns and refer to the same set of columns.

Find duplication of simple CHECK constraints on the same base table or foreign table column. Duplication of the same constraint means that if one starts to manage the code, then changes have to be made in multiple places. The problem is essentially similar with the data redundancy problem that database normalization tries to reduce. Do remember that the same task can be solved in SQL usually in multiple different ways. Thus, the exact copies are not the only possible duplication.

Find enumerated types that are not used in case of any base table column, domain, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code.

Find enumerated types with exactly the same values. There should not be multiple types that have the same values.

Find event triggers, which are not associated to a specific schema object.

Find excessive privileges on databases, schemas, domains, collations, sequences, foreign data wrappers, and foreign servers that are probably not needed by a typical application.

Find excessive privileges to use base tabes (for others than the owner of the base table). The excessive privileges are all that are not SELECT, INSERT, UPDATE, DELETE.

Find exclude constraints on base tables with multiple date/timestamp columns that prevent overlapping time periods.

Find exclude constraints that are based on the gist index type although the default b-tree index type would have been enough.

Find exclude constraints that implement a simple UNIQUE constraint. The checking might be slower compared to UNIQUE constraint.

Find expressions that use a data type formatting function - to_char, to_number, to_date, to_timestamp.