PostgreSQL Query Catalog

This query performs a quantity-based assessment of the database's procedural logic. It verifies whether the schema meets a minimum complexity requirement by counting the number of user-defined, non-trigger routines (functions and procedures). The check passes only if the count is equal to or greater than the threshold of 4. This metric is used to ensure a baseline level of backend logic implementation.

This query performs a semantic analysis of trigger function comments to identify potential violations of separation of concerns. It flags triggers whose documentation references explicit database operations (e.g., OP1, OP2) but lacks terminology associated with validation or invariant enforcement (e.g., "check", "ensure", "validate"). This linguistic pattern suggests that the trigger may be improperly implementing the business operation itself (a side effect) rather than serving its primary role as an integrity guardrail, or that the documentation inaccurately reflects the trigger's behavior.

This query provides a statistical analysis of identifier length across the schema. It calculates a frequency distribution by grouping base tables, views, and materialized views based on the character length of their names. The result is a count of how many objects exist for each distinct name length, which can be used to audit naming conventions or identify outliers.

This query provides a statistical overview of a potential naming convention issue by counting the number of user-defined database object identifiers that terminate in one or two numerical digits. This naming pattern is a design smell, often indicating bad database structure or semantic ambiguity. Instead of listing each individual name, the query returns an aggregate count, which is useful for quickly assessing the overall prevalence of this issue within the schema and tracking remediation progress over time.

This query retrieves a comprehensive list of all PRIMARY KEY and UNIQUE constraints defined on base tables within the database. These constraints are the fundamental mechanisms for enforcing entity integrity and uniqueness. The output provides essential information for schema auditing, documentation generation, and analyzing the data model's key structures. It allows administrators and developers to quickly verify how uniqueness is enforced for each table.

This query retrieves a comprehensive list of user-defined routines (functions and procedures) written in SQL or PL/pgSQL. It explicitly filters the result set to exclude:

• Trigger functions: Routines intended solely for use in triggers are omitted to focus on callable business logic.
  
• System schemas: Routines located in system-managed namespaces (e.g., pg_catalog, information_schema) are excluded to isolate user-created code.
  
• Extension routines.
  

This query retrieves the names of all columns defined with the BOOLEAN data type to facilitate an audit of naming consistency. The primary objective is to verify adherence to a recommended best practice: boolean column names should be prefixed with a semantic predicate, such as is_ (in English) or on_ (in Estonian). This convention enhances the self-documenting nature of the schema and improves the readability of SQL statements by framing the column's purpose as a true/false question.

This query validates column naming consistency, specifically targeting generic attributes such as comments or descriptions. It flags tables that exhibit a mixed convention: containing both prefixed (e.g., table_comment) and unprefixed (e.g., description) generic columns within the same database context. This excludes primary and foreign keys. The goal is to enforce a uniform style—either consistently prefixing generic columns with the table name or consistently omitting the prefix—rather than allowing a hybrid approach.

This query validates column naming conventions. It returns a row only for tables that have an inconsistent mix of column naming styles—specifically, where some columns (that are not part of a primary or foreign key) are prefixed with the table name and others are not.

Too many parameters (in this case four or more) could be a sign of not separating concerns and having a routine that has more than one task.

Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives an indication about the state of enforcing constraints at the database level.

Triggers can be used to maintain data integrity in a database by causing rejection of data that does not conform to certain rules. Therefore, the number of triggers in a database gives some indications about the state of enforcing constraints at the database level. The query does not count internal triggers.

Try to find missing foreign key constraints. Find base tables that do not participate in any referential constraint (as the referenced table or as the referencing table). These tables are like "islands" in the database schema.

Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, but have a name that reffers to the possibility that these are used to record references to a user. Exclude columns that have the default value CURRENT_USER or SESSION_USER.

Try to find missing foreign key constraints. Find columns of base tables that are not a part of any primary key, unique, and foreign key constraint, do not have an associated sequence generator, but have a name that reffers to the possibility that these are used to record some kind of codes or id's.

Try to find missing foreign key constraints. Find columns of base tables that are not covered by any foreign key constraint but belong to a composite key, do not have an associated sequence generator, and have a name that refers to the possibility that these are used to record some kind of codes or id's. Moreover, there must be at least one other base table that has a column with the same name. Such strategy would find missing constraints in tables that implement many-to-many relationship types but which that are not complete "islands" in terms of missing foreign key constraints.

Try to find missing foreign key constraints. Find non-key and non-foreign key columns of base tables that do not have an associated sequence generator, and that name refers to the possibility that the column holds parent identifiers.

Try to find missing foreign key constraints. Find pairs of base table columns that have the similar name, perhaps the same type, and that are not associated through a foreign key relationship.

Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database.

Try to use as much the possibilities of the DBMS as possible. On the other hand, do not install extensions that are not needed in order not to overcomplicate the database.