PostgreSQL Query Catalog

This query identifies logically flawed CHECK constraints on columns with timezone-unaware data types (DATE and TIMESTAMP WITHOUT TIME ZONE). It specifically targets constraints that contain timezone-aware logic (e.g., using AT TIME ZONE). This represents a critical semantic mismatch: the data type stores a "naive" or "local" time with no timezone context, while the constraint attempts to evaluate it within a specific timezone. The outcome of such a check is non-deterministic, as it depends on the session's current TimeZone setting, leading to unpredictable and unreliable data validation. This is a design flaw; if timezone-aware logic is required, the column should use the TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) data type.

This query identifies superfluous UNIQUE constraints where the constraint is logically subsumed by a more general EXCLUDE constraint on the same table. It targets cases where the set of columns in a UNIQUE or PRIMARY KEY constraint is a subset of (or equal to) the columns in an EXCLUDE constraint, provided the EXCLUDE constraint uses the equality operator (=) for those same columns. In this scenario, the EXCLUDE constraint already enforces uniqueness as part of its more complex logic, rendering the separate UNIQUE constraint redundant. Eliminating this duplication improves schema clarity and removes an unnecessary constraint check.

This query identifies inconsistencies in data validation for columns that share the same name across different tables. It flags cases where a conceptual data element (e.g., 'email', 'postal_code') is subject to a CHECK constraint in some tables but lacks one in others. This violates the principle of uniform data integrity, creates semantic ambiguity, and can allow invalid data to enter the system through the unconstrained columns.

This query identifies junction tables (implementing a binary relationship via two foreign keys) whose names are simple concatenations of the parent table names, often with minor variations (e.g., Courses_Lecturer). This naming convention is a design smell as it describes the physical implementation rather than the conceptual relationship being modeled. The recommended best practice is to rename such tables to reflect the domain concept they represent. For instance, the relationship between Course and Lecturer should be named after the activity it represents, such as Teaching or Course_Assignment.

This query identifies junction tables (implementing a binary relationship via two foreign keys) whose names are simple concatenations of the parent table names (e.g., Course_Lecturer). This naming style is flagged as a design smell because it merely describes the physical implementation rather than the conceptual relationship being modeled. The recommended best practice is to rename such tables to reflect the domain concept they represent. For instance, the relationship between Course and Lecturer should be named after the activity it represents, such as Teaching or Course_Assignment.

This query identifies a logical redundancy and likely data type misuse by finding CHAR columns that have a CHECK constraint on their value's length. The CHAR(n) data type is fixed-width and space-padded, meaning any non-NULL value will have a character length of exactly n. Therefore, a CHECK constraint on length is either superfluous (if it checks <= n) or will always fail (if it checks < n). This pattern indicates that the developer intended to store a variable-length string with a maximum length, for which the VARCHAR data type is the correct and more efficient choice.

This query identifies the semantic misuse of the CHAR(n) data type for non-foreign key columns where n > 1. It operates on a heuristic, flagging columns whose names suggest they store variable-length data (e.g., "name", "comment", "description", "email") rather than genuinely fixed-length data like standardized codes or hash values. Because CHAR(n) is a fixed-width, space-padded type, its use for variable-length strings is inefficient in terms of storage and can introduce application-level logic errors, making VARCHAR(n) the appropriate alternative.

This query identifies empty schemas within the database. A schema is considered empty if it exists as a namespace but contains no database objects, such as tables, views, functions, or types. The presence of such schemas often indicates artifacts from failed or incomplete migrations, obsolete application components, or setup errors, and they can be safely removed to reduce schema clutter.

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 identifies a semantic mismatch between a column's name and its data type. It flags base table columns that adhere to a predicate-based naming convention (i.e., starting with is_, has_, can_, or on_) but are not defined with the BOOLEAN data type. This is a design flaw as it forces developers to infer and manage truthiness through other types (e.g., INTEGER, CHAR(1)), which undermines schema clarity, requires data type coercion in queries, and can compromise data integrity by permitting non-boolean states.

This query identifies user-defined SQL and PL/pgSQL routines (excluding triggers) that exhibit a semantic inconsistency between their name and return type. It flags non-trigger routines whose names begin with a predicate prefix (e.g., is_, has_, can_, on_) but whose defined return type is not BOOLEAN. Such a mismatch violates the principle of self-documenting code, as the name creates a strong expectation of a TRUE/FALSE return value, which the routine's signature then contradicts.

This query identifies database identifiers (types, domains, columns, parameters) that likely violate the snake_case naming convention. It operates on a specific heuristic, flagging any name that begins with a common predicate prefix (is, has, on) but is not immediately followed by an underscore. This pattern is a strong indicator of camelCase (e.g., isActive) or PascalCase (e.g., IsActive) usage, both of which should be refactored to snake_case (e.g., is_active) to maintain a consistent and readable schema.

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).

This query audits the naming conventions of BOOLEAN columns, enforcing a predicate-based naming convention that begins with is_, has_, can_, or on_. It specifically discourages the use of simple adjectival or past participle forms as column names. For instance, is_agreement is the preferred form over agreed, and on_kinnitatud is preferred over kinnitatud. This standard ensures the column's name is an unambiguous true/false question, which improves schema self-documentation and the readability of SQL queries.

This query audits the naming conventions of routines that return a BOOLEAN data type. It identifies routines that do not adhere to the recommended semantic prefix convention (i.e., starting with is_, has_, can_, or on_). A function name should represent a state or a question (e.g., has_rights) rather than an action (e.g., check_rights). Enforcing this standard makes the routine's purpose and return value immediately obvious from its name.

This query audits the naming conventions of BOOLEAN columns to detect inconsistency. It checks whether the database contains a mix of styles: some boolean columns adhering to a semantic prefix convention (e.g., is_active, has_permission) and others using unprefixed nouns or adjectives (e.g., active, permission). The presence of both styles indicates a lack of a consistent standard, which can reduce schema clarity and predictability for developers.

This query identifies superfluous CHECK constraints where a programmatic length check duplicates a declarative, data type-based length limit. For instance, a CHECK constraint like char_length(column) <= 100 on a column already defined as VARCHAR(100) is redundant.

This query identifies expressions that use both the length() and char_length() functions, which is a strong indicator of a logical flaw. Since these functions are functional synonyms in PostgreSQL (both returning the character count), an expression that compares their results (e.g., length(col) > char_length(col)) may be either tautological or will always evaluate to false. Such code is typically written under the mistaken assumption that length() returns bytes, a behavior seen in other database management systems.

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.

This query identifies base table columns that, based on their name, are presumed to store personal names but lack an explicit maximum length constraint. It operates on a heuristic, flagging columns with names like first_name, surname, etc., that are defined with unbounded textual types (e.g., text, varchar) and have no corresponding CHECK constraint to limit their length (e.g., char_length(col) <= n). The absence of such a limit is a design flaw that can introduce usability issues in front-end applications and create potential security vulnerabilities related to excessive data submission.