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
581 Perhaps check constraint names contain incorrect or unnecessary words Find names of check constraints (either associated with a base table or a domain) that names contain words that are not needed in the name. For instance, constraints cannot ensure the correctness of data and thus the word correct should not be used in the names. Words like "valid" or phrases like "follows_rules" are just noise because all the constraint ensure that the registered data values are valid and follow certain rules. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
582 Perhaps CHECK should be associated with a domain Find cases where multiple columns with the same domain have exactly the same CHECK constraint that is directly associated with the table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
583 Perhaps Count(*) is wrongly used Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes Count aggregate function like this - Count(*), uses outer join, and grouping. In case of grouping you do not want to get an answer that an empty group contains one member. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
584 Perhaps excessive privileges to use base tables Find excessive privileges to use base tabes (for others than the owner of the base table). The excessive privileges are all that are not SELECT, INSERT, UPDATE, DELETE. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
585 Perhaps excessive privileges to use views Find non-SELECT privileges to use views (for others than the owner of the view). Perhaps there should be only the privilege to make queries (SELECT statements based on the views) and data modification takes place by using routines. REFERENCES and TRIGGER privileges are definitely not needed. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
586 Perhaps inconsistent use of temporal functions Find routines that use temporal functions CURRENT_TIMESTAMP, LOCALTIMESTAMP, or now() that is inconsistent with the default values of the columns that are used by the routine, e.g., function uses a column with the default value LOCALTIMESTAMP but the routine uses function CURRENT_TIMESTAMP or now(). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
587 Perhaps inconsistent use of temporal functions (2) Find as to whether in the same database more than one of these functions is used at the same time - now(), localtimestamp, current_timestamp. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
588 Perhaps incorrect check of permitted temporal values This query identifies potentially flawed CHECK constraints on columns of type timestamp or a timestamp range (e.g., tstzrange, daterange). It targets range checks where the upper bound of the value or the range is defined using an inclusive operator (<=). This is a common source of bugs, as a condition like column <= '2025-12-31' or UPPER(column) <= '2025-12-31' is interpreted as being up to 00:00:00 on that day, inadvertently excluding the entire last day of the intended period. The more robust pattern is to use an exclusive upper bound, such as column < '2026-01-01'. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 12:43 MIT License View
589 Perhaps incorrect use of 'NULL' Find Boolean expressions, queries, routines, and default values that refer to value 'NULL'. Perhaps NULL was intended instead. 'NULL' is a string (a value) but NULL is a special marker for denoting missing value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
590 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
591 Perhaps is not snake_case - id, code, key, or nr is not followed by an underscore Find names that perhaps do not use the snake_case naming style because the name starts with the phrase "id", "uuid", "code" , "kood", "key", or "nr" that is not followed by an underscore. Prefer snake_case over PascalCase and camelCase in names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
592 Perhaps is not snake_case - id, code, key, or nr is not preceded by an underscore Find names that perhaps do not use the snake_case naming style because the name ends with the phrase "id", "uuid", "code", "kood", "key", or "nr" that is not preceded by an underscore. Prefer snake_case over PascalCase and camelCase in names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
593 Perhaps is not snake_case - long subsections without underscores Find names that perhaps do not use the snake_case naming style because the name contains a long subsection (at least 20 characters) without underscores. Prefer snake_case over PascalCase and camelCase in names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
594 Perhaps last update time trigger is missing Find base tables that have a column for last update time but the table does not have associated before update row level trigger for changing the last update time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
595 Perhaps multiple surrogate keys This query identifies base tables with a potentially redundant key structure. It specifically targets those having more than one PRIMARY KEY or UNIQUE constraint defined on a single integer-type column whose name matches the surrogate key naming convention (id_* or *_id). This pattern is a strong indicator of multiple surrogate keys for the same entity, which can indicate an overcomplicated data model. A single entity should typically have only one system-generated identifier to maintain schema clarity, simplify join logic, and avoid redundancy. The presence of multiple such keys warrants a review to determine if one is superfluous. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 11:37 MIT License View
596 Perhaps searching based on a name instead of a code Find derived tables with a search condition that is possible based on a name instead of a code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
597 Perhaps spaces are unnecessarily restricted Find base table columns that name refers to the possibility that the column is used to record names or textual descriptions but the column seems to have a simple check constraint that restricts spaces in these. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
598 Perhaps the name referes to multiple concepts Find database objects that name contains words "and" (English) or "ja" (Estonian). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
599 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
600 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