| # | Name | Goal ▲ | Type | Data source | Last update | License | |
|---|---|---|---|---|---|---|---|
| 861 | Index FILLFACTOR is not default | This query generates a list of all indexes with an explicitly configured, non-default FILLFACTOR for the purpose of a performance audit. The query is aware of the different default FILLFACTOR values associated with various index access methods (e.g., 90 for B-tree, 100 for others like GiST/GIN). This allows administrators to quickly identify and review all instances of customized index storage parameters to assess if these non-standard configurations are justified and still effective. | General | system catalog base tables only | 2025-11-10 09:17 | MIT License | View |
| 862 | Perhaps the type of a base table column should be TSTZRANGE | This query identifies all base table columns defined with the range of timestamp without zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones. | Problem detection | INFORMATION_SCHEMA only | 2025-11-07 12:24 | MIT License | View |
| 863 | Perhaps the type of a base table column should be TIMESTAMPTZ | This query identifies all base table columns defined with the timestamp without time zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones. | Problem detection | INFORMATION_SCHEMA only | 2025-11-07 12:21 | MIT License | View |
| 864 | Checking the text maximum length with CHECK | 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. | General | system catalog base tables only | 2025-11-13 13:05 | MIT License | View |
| 865 | Avoid using length function | This query identifies all expressions that use the non-standard length() function. Although length() is a functional synonym for char_length() in PostgreSQL, its use is discouraged for two primary reasons: char_length() is the SQL-standard function, and length() has different semantics in other database systems (e.g., returning byte length in MySQL). To enhance code portability and prevent semantic ambiguity for developers, this query flags all instances of length() to encourage standardization on the char_length() function. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 12:53 | MIT License | View |
| 866 | Usage of the non-standard now() function | This query identifies all expressions that use the non-standard now() function. In PostgreSQL, now() is a historical, non-standard alias for the SQL-standard current_timestamp. While they are functionally identical within PostgreSQL (both returning the transaction start timestamp as a TIMESTAMPTZ), the use of current_timestamp is strongly preferred for reasons of code portability and adherence to standards. Standardizing on current_timestamp ensures the code is universally understood and easier to maintain or migrate to other database systems. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 10:01 | MIT License | View |
| 867 | Foreign keys with ON DELETE CASCADE | This query identifies all foreign key constraints that use ON DELETE CASCADE for the purpose of a design audit. The results must be manually reviewed to verify that each use case correctly implements a specific conceptual relationship. According to design principles, ON DELETE CASCADE is only appropriate for implementing generalization hierarchies (is-a relationships), compositions (strong ownership), or the existential dependency of a non-main entity on a main entity. Any usage outside of these patterns is considered a potential design flaw. | General | system catalog base tables only | 2025-11-08 10:51 | MIT License | View |
| 868 | Find views that can accept data modification statements | This query identifies all views against which data modification statements (INSERT, UPDATE, DELETE) can be executed without raising an error. It evaluates the is_insertable_into and is_updatable attributes to detect both "naturally" updatable views (where PostgreSQL automatically maps changes to base tables) and views made updatable via the rewrite rule system (specifically DO INSTEAD rules). Notably, this definition encompasses views defined with DO INSTEAD NOTHING rules; while such views do not physically modify data, they are technically considered updatable because they accept DML statements without failure. | General | INFORMATION_SCHEMA only | 2025-12-13 12:34 | MIT License | View |
| 869 | CHECK constraints are inconsistent with DEFAULT values | This query identifies a logical contradiction between data validation rules and default value definitions. It flags CHECK constraints involving two columns where both columns share the same DEFAULT value, yet the constraint enforces a strict inequality (e.g., col1 < col2) or non-equality (col1 <> col2). This configuration is logically flawed because attempting to insert a row using the default values for both columns will result in an immediate constraint violation (as X < X evaluates to false). This renders the default values mutually exclusive and unusable in practice. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-14 13:28 | MIT License | View |
| 870 | Perhaps an unsuitable use of CHAR(n) type in base tables (based on check constraints) | 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. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 15:08 | MIT License | View |
| 871 | Columns with BOOLEAN type that do have a good name | 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). | General | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 13:58 | MIT License | View |
| 872 | Incorrect field size (based on default values) | This query identifies a potential schema mismatch regarding data precision. It flags base table columns that default to CURRENT_USER or SESSION_USER but define a character length differing from the PostgreSQL standard identifier limit (typically 63 bytes, defined by NAMEDATALEN - 1). Risk (Length < 63): Poses a hard runtime failure risk if a username exceeds the defined length. Inefficiency (Length > 63): Indicates imprecise modeling, as the stored value can technically never exceed the system limit. |
Problem detection | INFORMATION_SCHEMA only | 2025-12-15 11:09 | MIT License | View |
| 873 | Perhaps the type of a base table column should be BOOLEAN (based on column names) | 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. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-13 14:07 | MIT License | View |
| 874 | Perhaps incorrect column name (based on default values) | This query identifies a semantic mismatch between column definitions and their identifiers. It flags base table columns that are configured with a DEFAULT value of CURRENT_USER or SESSION_USER (indicating they store user identity) but whose names fail to reflect this purpose. Specifically, it searches for columns lacking semantic cues such as "user", "login", "owner", or "by" in their names. This obscuration reduces schema self-documentation, as developers cannot intuitively determine that the column is intended for audit or ownership tracking. | Problem detection | INFORMATION_SCHEMA only | 2025-12-14 13:09 | MIT License | View |
| 875 | CHECK constraint with pattern matching on non-textual columns | This query identifies a semantic mismatch between data types and constraint logic. It targets base and foreign table columns that are defined with non-textual data types (e.g., INTEGER, DATE, BOOLEAN) but are subject to single-column CHECK constraints utilizing string pattern matching operators (LIKE, SIMILAR TO, or regular expressions). This practice forces implicit casting to text, which is computationally inefficient and indicates a design flaw. It suggests that either the column should utilize a textual data type, or the constraint should be rewritten using operators appropriate for the actual data type (e.g., numeric ranges instead of regex). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-05 19:13 | MIT License | View |
| 876 | Inappropriate use of trim function in whitespace constraints | This query identifies a semantic mismatch between the name and implementation of CHECK constraints (on tables, foreign tables, or domains). It targets constraints whose names suggest they validate against whitespace-only strings (e.g., names containing 'whitespace', 'space', 'blank'), but whose logic inappropriately uses the trim() function. The trim() function is a formatting tool for removing leading/trailing spaces, not a validation tool for ensuring a string is not composed entirely of whitespace. This indicates a likely implementation error, as a more robust regular expression (e.g., column !~ '^\s*$') is the correct tool for this type of validation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-15 12:34 | MIT License | View |
| 877 | Base table column of measurements does not have a correct data type | This query identifies a semantic mismatch in data type selection for columns intended to store measurement data. It targets columns whose names imply a quantitative measurement (e.g., "length", "weight", "count", excluding boolean prefixes like "is_") but are not defined with a numeric data type (INTEGER, NUMERIC, etc.). Storing measurements as text (VARCHAR) prevents mathematical operations, aggregation, and proper sorting, and is considered a design flaw. | Problem detection | INFORMATION_SCHEMA only | 2025-12-03 19:19 | MIT License | View |
| 878 | Semantic mismatch: non-textual data types for phone numbers | This query identifies a semantic mismatch in data type selection for columns intended to store telephone numbers. It flags columns whose identifiers imply phone number content (e.g., names containing "phone", "mobile", "telef") but are defined with non-textual data types (e.g., INTEGER, NUMERIC, BIGINT). Telephone numbers are semantically strings, as they may contain leading zeros, international prefixes (+), and formatting characters (-, (), ext.), and are not subject to arithmetic operations. Storing them as numeric types leads to data loss (truncation of leading zeros) and formatting inflexibility. | Problem detection | INFORMATION_SCHEMA only | 2025-11-27 10:35 | MIT License | View |
| 879 | Perhaps the type of a base table column/domain should be BOOLEAN (based on CHECK constraints) | This query identifies base table columns and domains that utilize CHECK constraints to simulate boolean logic on non-boolean data types. It targets constraints that restrict the domain of permitted values to binary sets, such as {0, 1}, {'Y', 'N'}, {'T', 'F'}, or string literals like 'true'/'false'. While functional, this "pseudo-boolean" pattern is considered suboptimal in PostgreSQL. The native BOOLEAN data type is preferred for its storage efficiency, semantic clarity, and built-in support for logical operators, rendering such manual constraints unnecessary. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-12-14 14:37 | MIT License | View |
| 880 | Unbounded textual columns for non-descriptive attributes | This query identifies base table columns defined as unbounded TEXT or VARCHAR (without a length specifier) that lack any corresponding CHECK constraint to restrict value length. It explicitly excludes foreign key columns and columns heuristically identified as descriptive fields (e.g., names containing "comment", "description", "note"), where arbitrary length is typically acceptable. For structured attributes (such as names, codes, or identifiers), relying on unbounded types without constraints is a design risk, potentially allowing excessive data payload, complicating index usage, and violating domain constraints. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2025-11-27 10:19 | MIT License | View |