PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name Goal ▲ Type Data source Last update License
881 CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that do not explicitly specify a time zone This query identifies CHECK constraints on TIMESTAMPTZ and TSTZRANGE columns that use timestamp literals with fixed UTC offsets instead of named time zones. This practice is flagged as a design flaw because fixed offsets do not account for Daylight Saving Time (DST), leading to constraints on both single timestamps and range boundaries that are unable to correctly represent a local time zone's rules throughout the entire year. Problem detection INFORMATION_SCHEMA only 2025-11-19 16:31 MIT License View
882 Superfluous IS NULL checks in constraints This query identifies CHECK constraints that contain redundant logic for handling NULLs, a pattern often arising from a misunderstanding of SQL's three-valued logic (TRUE, FALSE, UNKNOWN). A CHECK constraint's condition only fails on FALSE, implicitly permitting NULLs by evaluating comparisons to UNKNOWN. Consequently, an explicit OR column IS NULL clause is tautological and adds no functional value, as demonstrated by the equivalence of CHECK (price IS NULL OR price > 0) and CHECK (price > 0). Removing this superfluous logic improves constraint clarity and eliminates a marginal but unnecessary computational step. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 10:19 MIT License View
883 Columns with array or user-defined type 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. General system catalog base tables only 2025-12-12 17:23 MIT License View
884 Range lower bound can be NULL This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL lower bound. This highlights ranges that can be "unbounded" on their starting side, which may be unintentional and could impact query logic and data constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 12:03 MIT License View
885 Range upper bound can be NULL This query identifies columns of base tables that use a RANGE data type but are configured to permit a NULL upper bound. This highlights ranges that can be "unbounded" on their ending side, which may be unintentional and could impact query logic and data constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 12:03 MIT License View
886 Optional base table columns that have a default value This query identifies columns that are both NULLable and have a DEFAULT value. This configuration represents a semantic contradiction: the DEFAULT clause implies that a value should always exist for the column, while the absence of a NOT NULL constraint explicitly permits the absence of a value. The presence of a DEFAULT strongly suggests the column's business logic requires a value, and therefore it should be defined with a NOT NULL constraint to enforce this consistently and make the schema's intent unambiguous. Problem detection INFORMATION_SCHEMA only 2025-11-12 19:04 MIT License View
887 Columns with a range type that require a better name This query identifies columns with a RANGE data type that violate naming conventions. It flags columns whose names do not semantically suggest a range or period, which can create ambiguity and lead to incorrect assumptions when writing queries. Problem detection INFORMATION_SCHEMA only 2025-11-07 12:30 MIT License View
888 All derived tables that use joining tables This query identifies complex derived tables (views and materialized views) that perform data integration operations. Specifically, it filters for views whose definition involves joining two or more distinct tables. This distinguishes non-trivial views—which encapsulate relationship logic and data aggregation—from simple projection views that merely mirror a single base table. The result highlights the core reporting and data integration layer of the schema. General INFORMATION_SCHEMA+system catalog base tables 2025-12-26 10:02 MIT License View
889 Explicit locking is missing This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-15 12:45 MIT License View
890 Explicit locking is missing (2) (ChatGPT version) This query identifies concurrency risks in user-defined routines by flagging INSERT, UPDATE, or DELETE statements that utilize subqueries without a FOR SHARE locking clause. Failure to acquire a shared lock on source rows allows concurrent transactions to modify or delete them between the subquery's execution and the outer operation, potentially leading to data inconsistencies. Routines utilizing the xmin system column are excluded, operating on the assumption that they implement Optimistic Concurrency Control (version checking) and therefore do not require pessimistic locking. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-15 12:41 MIT License View
891 Not enforced constraints This query identifies constraints (CHECK and FOREIGN KEY) that exist in the system catalog but are not actively enforced against the table data. Problem detection system catalog base tables only 2025-11-15 10:10 MIT License View
892 Snake_case violations detected by common suffixes This query identifies database identifiers (columns, parameters, etc.) that likely violate the snake_case naming convention based on suffix analysis. It flags names ending with common temporal or attributional terms (date, time, by) where the suffix is not immediately preceded by an underscore. This pattern is highly indicative of camelCase (e.g., createdDate, updatedBy) or PascalCase usage. Adhering to snake_case (e.g., created_date, updated_by) is the recommended standard for SQL database schemas to ensure consistency and readability. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-27 11:13 MIT License View
893 Perhaps is not snake_case - Boolean-indicating prefix without underscore 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:01 MIT License View
894 Perhaps un-trimmed string concatenation in derived tables This query identifies derived tables (views and materialized views) containing string concatenation logic that introduces potential leading or trailing whitespace. It targets expressions using the concatenation operator (||), concat(), or format() that may inject separators (such as spaces) but lack a surrounding trim() function. This pattern often results in "dangling separators" when one of the concatenated components is null or empty, degrading data quality and presentation. Problem detection system catalog base tables only 2025-12-22 18:28 MIT License View
895 Derived tables with embedded row locking This query identifies derived tables (views and materialized views) whose defining subqueries utilize explicit row locking clauses (e.g., FOR UPDATE, FOR SHARE). Embedding locking semantics within a view definition is considered an architectural anti-pattern. It couples data projection with transaction control, causing simple read operations against the view to unexpectedly acquire locks. This behavior degrades concurrency by blocking other readers and violates the principle that reading a data element should not implicitly block simultaneous access. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-10 12:58 MIT License View
896 Perhaps USING syntax could be used for joining in the subqueries of derived tables This query identifies derived tables (views) that utilize the explicit ANSI SQL-92 join syntax with the ON clause. It specifically targets views where join conditions are defined within the FROM clause but explicitly excludes those using the simplified USING syntax. This helps to identify derived tables that subquery could be simplified. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-03 15:57 MIT License View
897 Empty schemas 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 14:15 MIT License View
898 Length and char_length functions are used within the same expression 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:12 MIT License View
899 Column names that make joining tables more difficult (quite similar names) This query identifies foreign key columns where the identifier deviates slightly from the referenced candidate key, specifically exhibiting a textual difference (Levenshtein distance) of two to four characters. This range typically captures minor prefixes (e.g., fk_) or suffixes that prevent the use of the concise SQL USING syntax in join operations. The query explicitly excludes self-referencing constraints (recursive relationships), where distinct column names are structurally mandatory. Aligning these names allows for cleaner, more readable query formulation. Problem detection system catalog base tables only 2025-12-14 11:29 MIT License View
900 Column names that make joining tables more difficult This query identifies foreign key columns where the identifier differs from the referenced candidate key identifier. It explicitly excludes self-referencing constraints (recursive relationships), where name divergence is structurally mandatory. The primary objective is to identify opportunities to harmonize column names across the schema. Synchronizing the foreign key name with the referenced column name facilitates the use of the ANSI SQL USING clause in join operations (e.g., JOIN t1 USING (client_id)), which is significantly more concise than the explicit ON predicate required when names differ. Problem detection system catalog base tables only 2025-12-14 11:31 MIT License View