| 781 |
Rules with the same name in different schemas |
Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication. |
General |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 782 |
Rules with the same name within the same schema |
Find names of rules that are used within the same schema more than once. Give different triggers different names. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 783 |
search_path should not be between quotation marks |
Write security definer functions securely. Give to the DBMS correctly information about the sequence of schemas that constitute the search path. You shouldn't write search path value between quotation marks or apostrophes. Thus, instead of writing SET search_path = "public, pg_temp"; or SET search_path = 'public, pg_temp'; you should write SET search_path = public, pg_temp; |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 784 |
SECURITY DEFINER procedures cannot end transactions |
You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 785 |
SECURITY INVOKER routines that access data |
Find SECURITY INVOKER routines that read rows from a table, add rows to a table, update rows in a table, or delete rows from a table. Better to have for these purposes SECURITY DEFINER routines, which make it possible to give to the users privileges to only execute routines without having rights to access their underlying tables. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 786 |
SELECT * in a routine body |
SELECT statement should list the columns not use SELECT * to return data from all the columns. Firstly, it ensures, that the query asks only data that is really needed by the routine. It means less data that the DBMS has to fetch and pass to the routine. It could also mean that the DBMS can answer to a query based on an index without reading table blocks. Secondly, it documents the data that is returned by the query. The query does not consider objects that are a part of an extension. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 787 |
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 |
| 788 |
Sequence generators not needed |
Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 789 |
Sequences that are not owned by a table column |
Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 790 |
SET DEFAULT compensatory action is unsuitable |
Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 791 |
SET NULL compensatory action is unsuitable |
Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value). |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 792 |
Set operations that do not remove duplicate rows in derived tables |
Find derived tables (views and materialized views) that use a set theoretic operation (union, except or intersect) in a manner that does not remove duplicate rows and thus can produce a multiset not a set. Make sure that it is what is needed. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 793 |
Short cycle (columns) |
Find cases where two candidate keys of the same table that are also foreign keys reference to each other. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 794 |
Short cycles (tables) |
Find pairs of tables that have both a mandatory (NOT NULL) and not defrerrable foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-07 10:11 |
MIT License |
View |
| 795 |
Should the time zone be recorded in case of time or not? |
Find all the base table columns that have the type time without time zone or time with time zone. Return the data only if there is at least one column with the type time without time zone and one column with the type time with time zone. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 796 |
Should the time zone be recorded in case of timestamp or not? |
Find all the base table columns that have the type timestamp without time zone or timestamp with time zone. Return the data only if there is at least one column with the type timestamp without time zone and one column with the type timestamp with time zone. |
Problem detection |
INFORMATION_SCHEMA only |
2025-11-07 10:11 |
MIT License |
View |
| 797 |
Simple check constraints with multiple tasks |
Find simple check constraints, i.e., check constraints that cover one column that seem to have multiple tasks. The corresponding code smell in case of cleaning code is "G30: Functions Should Do One Thing". (Robert C. Martin, Clean Code) |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 798 |
Simple primary keys that column name does not contain the table name |
Find simple primary keys that column name does not contain the table name. The naming should be clear and consistent. |
Problem detection |
system catalog base tables only |
2025-11-07 10:11 |
MIT License |
View |
| 799 |
Simplify regex by combining alpha and digit classes |
This query identifies regular expressions that can be simplified by consolidating separate character class references. It specifically targets patterns that explicitly match both alphabetic characters ([:alpha:]) and numeric digits ([:digit:], \d, or [0-9]) as separate components within a larger character set (e.g., [[:alpha:][:digit:]]). These distinct classes can be refactored into the single, more concise POSIX character class [:alnum:], which logically represents the union of both. Performing this simplification improves the readability and compactness of the regular expression without altering its behavior. |
Problem detection |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-19 17:38 |
MIT License |
View |
| 800 |
Single-column natural primary keys |
This query identifies primary keys that consist of a single column and are not system-generated (i.e., they are not associated with a sequence or defined as IDENTITY columns). This pattern is characteristic of a natural primary key, where the key's value is derived from a real-world, user-defined attribute rather than an arbitrary surrogate value. Identifying these keys is crucial for auditing a data model's key strategy and understanding its reliance on meaningful, potentially mutable, business data for entity identification. |
General |
INFORMATION_SCHEMA+system catalog base tables |
2025-11-15 12:50 |
MIT License |
View |