| 961 |
Trigger functions with a conditional statement |
Find trigger functions that contain a conditional (IF or CASE) but do not contain a SELECT statement before these. The latter condition is for the reason that one cannot use a subquery in the WHEN clause. Thus, if one wants to make a query and decide the further action based on the results of the query, then one must do it within the body of the function. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 962 |
Trigger is used to enforce referential integrity |
Find tables where user-defined (non-system) triggers are used to implement referential integrity. In addition to table name show the triggers and the number of triggers. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 963 |
Trigger routines with TG_OP variable that are not associated with a suitable trigger |
Automatically defined TG_OP variable in a trigger function has data type text. Its value is a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired. Find the routines that according to the TG_OP value must react to a certain operation but the routine is not associated with any triggers that are fired by the operation. For instance, the routine specifies reaction to DELETE operation but the routine is not associated with any DELETE trigger. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 964 |
Triggers that are used to calculate tsvector values react to a wrong set of events |
Find triggers on base tables that are used to calculate tsvector values that react to a wrong set of events, i.e., react to the DELETE event or do not react to the INSERT and UPDATE events. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 965 |
Triggers with arguments from the CREATE TRIGGER statement |
Find triggers that get an argument from the CREATE TRIGGER statement. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 966 |
Triggers with SELECT (i.e., probably check data based on another table) |
This query identifies trigger functions intended for constraint enforcement that are susceptible to concurrency anomalies due to PostgreSQL's Multi-Version Concurrency Control (MVCC) model. Since read operations (SELECT) do not block write operations, a trigger that validates cross-row constraints without acquiring explicit locks (e.g., LOCK TABLE or SELECT ... FOR UPDATE) involves a race condition. The query detects triggers that query auxiliary data. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2026-01-19 15:19 |
MIT License |
View |
| 967 |
Triggers with the same name in different schemas |
Find trigger names that are used in a database in more than one schema. Different things should have different names. But here different triggers have the same name. Also make sure that this is not a duplication. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 968 |
Triggers with the same name within the same schema |
Find names of triggers that are used within the same schema more than once. Give different triggers different names. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 969 |
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 |
| 970 |
Tsvector values are not automatically calculated |
Find base table columns with tsvector type in case of which it is not a generated column nor does the table has an associated trigger to calculate the tsvector value automatically. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 971 |
Unbalanced brackets |
Write expressions correctly. Find code fragments that have unbalanced brackets, i.e., the number of opening brackets is not the same as the number of closing brackets. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 972 |
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 |
2026-01-19 14:10 |
MIT License |
View |
| 973 |
Unique constraints made redundant by an exclude constraint |
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. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 09:56 |
MIT License |
View |
| 974 |
Unique index definition instead of a key declaration |
This query identifies semantic abstraction violations where data uniqueness is enforced via low-level CREATE UNIQUE INDEX statements instead of declarative PRIMARY KEY, UNIQUE, or EXCLUDE constraints. It specifically targets "plain" unique indexes (excluding partial or expression-based indexes) that are functionally identical to standard constraints. According to the ANSI-SPARC architecture, indexes belong to the internal (physical) schema, while constraints belong to the conceptual schema. Therefore, defining business rules using high-level constraint syntax is preferred for semantic clarity and architectural correctness. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-01-19 14:37 |
MIT License |
View |
| 975 |
Unlogged tables |
Find unlogged tables. These may improve the performance of INSERT operations, but with the price of possibly loosing data - an unlogged table is automatically truncated after a crash or unclean shutdown. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 976 |
Unnamed columns in routines |
Find user-defined routines that contain a SQL statement that does not give the name to a column in a SQL statement. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 977 |
Unnamed parameters |
Find unnamed parameters in PL/PGSQL routines that do not declare aliases for parameters and in SQL routines. Avoid unnamed parameters because dependency on position in case of referencing the parameters makes evolving the code more difficult. In case of unnamed parameters - if one changes the order of parameters in the routine signature, then one has to change the body of the routine in order to use correct references. The bigger is the number of parameters in a routine the more the unnamed parameters make it more difficult to understand the routine. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 978 |
Unnecessary domains |
Domain is a reusable artifact. Effort of its creation should be paid off by the advantages that it offers. If a domain is used in case of at most one column of a base table or even if it is used in case of more than one column but it does not specify neither a default value nor a check constraint, then there is no point of creating the domain. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 979 |
Unnecessary explicit locking in read-only routines |
This query identifies read-only routines that unnecessarily utilize explicit locking mechanisms. PostgreSQL's Multi-Version Concurrency Control (MVCC) ensures that readers do not block writers (and vice versa) for standard query operations. Therefore, routines that perform no data modification (DML) and do not raise exceptions have no functional need to acquire table-level (LOCK TABLE) or row-level (FOR SHARE/UPDATE) locks. Using them in this context provides no benefit while actively degrading system concurrency by blocking other transactions. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-12-10 13:23 |
MIT License |
View |
| 980 |
Unnecessary NULL checks in single-column constraints |
This query identifies single-column CHECK constraints (applied either directly to a base table or through a domain) that explicitly check for NULL values. Constraint definitions should be kept as simple as possible. Because a NULL value causes a logical condition to evaluate to UNKNOWN, and CHECK constraints inherently allow rows that evaluate to either TRUE or UNKNOWN, there is no need to explicitly allow missing values. For instance, instead of writing CHECK (price > 0 OR price IS NULL), the constraint should simply be written as CHECK (price > 0). |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2026-06-02 13:55 |
MIT License |
View |