PostgreSQL Query Catalog

Find base tables with more than one comment columns

Find base tables with more than one name columns. Perhaps the normalization level of the table is low.

Find base tables that have more than one column with a temporal type (date or timestamp).

Find base tables that have 1000 rows or more.

Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables.

Find primary key, unique, foreign key, and exclude constraints that have been defined in tables that participate in an inheritance hierarchy. Do not forget to redefine the constraints that are defined on supertables also on their subtables.

Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern).

This query identifies referential paths (chains of foreign key relationships) where every constraint in the path is configured with ON DELETE CASCADE. The analysis is intended to uncover potential transitive deletion risks, where a single DELETE operation on a root table could trigger a catastrophic, cascading data loss across multiple, deeply-nested tables. The length of such chains is a key indicator of architectural fragility.

Check as to wheteher the names of table columns are in the plural or in the singular form. Make sure that you are consistent in naming.

Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.

Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.

Check as to wheteher the names of routine parameters are in the plural or in the singular form. Make sure that you are consistent in naming.

Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.

Check as to wheteher the names of tables are in the plural or in the singular form. Make sure that you are consistent in naming.

Find check constraints that involve columns with the type Boolean.

This query identifies CHECK constraints applied to base or foreign table columns that store personal names. It flags these constraints for review to ensure they are not overly strict. Many regular expressions or character limits fail to account for the complexity of real-world names (such as those containing apostrophes, hyphens, or diacritics), thereby inadvertently preventing the registration of valid legal names.

If your table contains columns with temporal data, then it will be appropriate to restrict the range of possible values in these columns because some of the values that belong to the type might not be appropriate (for instance, imagine a client who was born in 1100-12-03 or a contract that was registered in 3890-12-12- 12:45). If your table contains multiple columns with temporal data that denote events, then the rule about the order of the events must be enforced, if possible.

Discover incorrect usage of non-deterministic functions in CHECK constraints. Find base table columns and foreign table columns that have a CHECK constraint that refers to a non-deterministic function that returns current date/time/timestamp.

Find multicolumn CHECK constraints. Such constraints must be associated directly with a base table, i.e., these cannot be associated with a domain. Enforce as much data integrity as possible at the database level and prefer declarative constraints to a trigger.

CHECK constraints with the cardinality bigger than one that involve the same set of columns. Make sure that there is no duplication.