PostgreSQL Query Catalog

This query identifies all base table columns that enforce a maximum value length through the use of a CHECK constraint. It specifically searches for constraint definitions that contain length-calculating functions (such as length(), char_length(), or similar patterns) to provide a comprehensive list of all columns where data length is explicitly managed by a business rule at the database level.

Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent.

Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent.

Find columns that have been added to a subtable, i.e., these were not defined in its immediate supertable.

Find base table columns that based on the names and data types are meant for registering registration time or update time. Make sure that the columns have the same properties.

This query identifies columns defined with complex data types, specifically Arrays or User-Defined Types (UDTs). While PostgreSQL supports these advanced features, their indiscriminate use often signals an over-engineered schema. The query serves as a prompt to audit these columns and verify that the complex type is strictly necessary for performance or domain logic, and that a standard relational structure (e.g., scalar types or a child table) would not be a more appropriate and flexible design choice.

This query identifies and lists all BOOLEAN columns that conform to the established predicate-based naming convention. It returns columns whose names begin with one of the approved semantic prefixes: is_, has_, can_, or on_. The output serves as a report of schema components that correctly adhere to best practices for clarity and self-documentation, turning column names into unambiguous true/false questions (e.g., is_agreement instead of agreed).

Find columns with BYTEA or OID type. These columns are potentially meant for storing large objects. Each columns should have the most appropriate data type.

Find columns with JSON, JSONB, or XML type. Each columns should have the most appropriate data type.

Find columns of base tables and materialized views that have tsvector type.

Find all comments of columns of tables.

Find comments of derived tables (views and materialized views) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, triggers, rules). Make sure that the comments give relevant, useful, and correct information.

Find comments of non-derived tables (base tables, foreign tables, and partitioned tables) that are registered in the system catalog witht a COMMENT statement. Find also comments on their associated objects (columns, constraints, indexes, triggers, rules). Make sure that the comments give relevant, useful, and correct information.

Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information.

Find all the comments that have been added with a COMMENT statement to schemas, sequences, types, domains, and event triggers.

Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table.

Find all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent.

Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments.

Find the names on base table columns that are a part of a candidate key but not a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name.

Find the names on base table columns that are not a part of a candidate key and a foreign key and contain a generic word (nimi, nimetus, kommentaar, kirjeldus, name, comment, description, etc.). Make sure that naming of these is consistent, i.e., such names always contain the table name or never contain the table name.