PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 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
61 Explicit locking This query identifies user-defined routines that employ explicit locking mechanisms to supplement PostgreSQL's default Multi-Version Concurrency Control (MVCC). It detects the presence of table-level locking (LOCK TABLE) or explicit row-level locking clauses (e.g., SELECT ... FOR UPDATE, FOR SHARE). While MVCC generally provides sufficient isolation for concurrent transactions, explicit locking is necessary in specific race-condition scenarios. This inventory assists in auditing concurrency control strategies and detecting potential sources of deadlocks or serialization bottlenecks. General INFORMATION_SCHEMA+system catalog base tables 2025-12-10 13:13 MIT License View
62 Trying to lock a value instead of a row This query identifies SQL routines that utilize explicit row locking clauses (e.g., FOR UPDATE, FOR SHARE) in queries that do not target a specific base table or relation. For instance, a statement like SELECT 'text' AS v FOR UPDATE attempts to apply a lock to a scalar constant. Since row-level locks in PostgreSQL require a physical row version (tuple) within a table to be effective, such statements are semantically void. They indicate a fundamental misunderstanding of the concurrency control mechanism and should be corrected to target actual table rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-10 13:00 MIT License View
63 Invalid explicit locking with aggregate functions This query identifies SQL statements that attempt to apply explicit row locking (e.g., FOR SHARE, FOR UPDATE) to the result of an aggregate function (e.g., COUNT(*)). This is a semantic error because locking clauses operate on specific physical rows, whereas aggregate functions return a derived scalar value that is decoupled from the underlying row versions. To correctly enforce a lock, the query must select the specific columns (typically the primary key) of the target rows, rather than a computed aggregate. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-10 12:59 MIT License View
64 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
65 Data type mismatch in check constraints This query identifies single-column CHECK constraints where the validation logic utilizes operators or functions that are incompatible with the column's native data type. It detects cases where the database must perform implicit casting to evaluate the expression (e.g., performing arithmetic on a TEXT column or string manipulation on a DATE column). Relying on implicit coercion in constraints involves unnecessary computational overhead and frequently indicates a fundamental error in data modeling or constraint formulation. Problem detection system catalog base tables only 2025-12-05 19:23 MIT License View
66 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
67 Inconsistent CHECK constraints for same-named columns This query audits the schema for inconsistent data validation logic. It identifies non-foreign key base table columns that share the same identifier (name) across different tables but enforce a divergent set of single-column CHECK constraints. According to the "Clean Code" principle of consistency, a recurring attribute name (e.g., status_code) implies a shared domain concept and should be subject to identical validity rules globally. Discrepancies indicate that business rules are being applied unevenly, potentially compromising data integrity. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 19:02 MIT License View
68 Useless trivial trigger functions This query identifies trigger functions that are functionally trivial, specifically those whose sole action is to execute RETURN NEW. In a BEFORE trigger context, this operation simply allows the data modification to proceed unchanged. If the function contains no other logic (e.g., validation, modification of NEW, or side effects), it performs no useful work and incurs unnecessary execution overhead. Such triggers are likely incomplete placeholders or obsolete code that should be removed. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 11:50 MIT License View
69 Useless trivial non-trigger functions This query identifies user-defined routines (excluding triggers) that are functionally trivial. It flags routines whose body consists solely of returning a static value: either an input argument (identity function), a constant literal, or NULL. Such routines typically perform no computation, data manipulation, or side effects. They are likely placeholders, deprecated logic, or artifacts of incomplete refactoring, and should be reviewed for removal or implementation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 11:44 MIT License View
70 Inconsistent prefixing of specific (non-generic) column names This query audits the database for inconsistent column naming conventions, specifically focusing on non-generic, domain-specific attributes (excluding primary keys, foreign keys, and generic fields like 'comment'). It detects a hybrid naming strategy: some columns are prefixed with the table name (e.g., employee_salary) and others are not (e.g., hiring_date). This inconsistency suggests a lack of a standardized data dictionary, making query writing unpredictable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 10:15 MIT License View
71 Inconsistent use of gratuitous context in the names of non-foreign key and non-primary key columns 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 10:12 MIT License View
72 Gratuitous context in the names of non-foreign key and non-primary key columns This query identifies base table columns that unnecessarily include the table name. It searches for columns that are not part of a primary or foreign key and contain the name of their parent table. To avoid flagging legitimate naming conventions, it explicitly excludes a list of generic column names (e.g., name, description, nimi, kommentaar) where prefixing with the table name is considered good practice for improving clarity in queries. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 10:01 MIT License View
73 Inconsistent prefixing of generic column names 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-05 09:44 MIT License View
74 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
75 Unnecessary use of Coalesce This query identifies redundant null-handling logic within string manipulation expressions. It targets the use of explicit coalesce() calls nested inside functions that are already null-safe, such as concat(), concat_ws(), or format(). Since these functions implicitly treat NULL arguments as empty strings (or ignore them), wrapping arguments in coalesce(arg, '') is superfluous. The query deliberately excludes expressions using the standard concatenation operator (||), as coalesce() is legitimately required in that context to prevent null propagation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-03 15:58 MIT License View
76 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
77 Insufficient number of user-defined views This query assesses the use of abstraction layers within the database schema. It verifies that there are at least 4 user-defined views present. Views are essential for simplifying complex queries, restricting data access, and presenting specific data perspectives to applications. Meeting this threshold indicates a sufficient implementation of data abstraction and query encapsulation. Problem detection INFORMATION_SCHEMA only 2025-11-30 09:38 MIT License View
78 Insufficient number of user-defined triggers+rules This query assesses the extent of active logic implementation within the database schema. It verifies that there are at least 3 user-defined triggers or rewrite rules present in the database. These objects represent automated tasks that for example enforce complex integrity constraints. Meeting this threshold indicates a baseline competency in utilizing PostgreSQL's event-driven capabilities. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-30 09:36 MIT License View
79 Insufficient number of user-defined triggers+rules (based on number of tasks) This query assesses the extent of active logic implementation within the database schema. It verifies that there are user-defined triggers or rewrite rules present for at least 3 tasks. These objects represent automated tasks that for example enforce complex integrity constraints. Meeting this threshold indicates a baseline competency in utilizing PostgreSQL's event-driven capabilities. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-30 09:34 MIT License View
80 Insufficient number of user-defined foreign tables This query assesses the utilization of PostgreSQL's Foreign Data Wrapper (FDW) capabilities. It verifies the existence of at least 2 user-defined foreign tables within the database schema. This requirement ensures that the implemented solution demonstrates the ability to integrate and query data from external sources, extending the data model beyond local storage. Problem detection INFORMATION_SCHEMA only 2025-11-30 09:31 MIT License View