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
701 STATEMENT level triggers that refer to the values of row variables NEW or OLD Find STATEMENT level triggers that refer to the values of row variables NEW or OLD. NEW and OLD are special variables that can only be used in row-level trigger procedures. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
702 Stating the obvious Find database objects that name contains words "data" or "info". These are noise words because databases are meant for storing and manipulating data/information. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
703 Stating the obvious (2) Find the names of database objects where the name of the database object contains a part of the name of the object type. For instance, the query finds base tables, were the name contains fragments _base, base_, _table, or table_. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
704 Subqueries of derived tables with LIMIT/FETCH/DISTINCT ON without ORDER BY Find subqueries of derived tables (views, materialized views) with the LIMIT/FETCH clause or with DISTINCT ON construct but without the ORDER BY clause. These constructs require sorting to produce a meaningful result. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
705 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
706 Surrogate key columns Find surrogate keys. Surrogate key is a key that consist of one column, which has an integer type. The key has been declared by using PRIMARY KEY or UNIQUE constraint. The column is associated with a sequence generator (either external or internal, i.e., created by the system automatically because the column has been declared as an identity column). The column does not participate in any foreign key. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
707 Surrogate key columns that do not follow the naming style Find surrogate key columns that name does not end with "id_" or start with "id_". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
708 Surrogate keys using non-standard SERIAL pseudo-type This query identifies surrogate key columns defined using the legacy, PostgreSQL-specific SERIAL (or BIGSERIAL) pseudo-type. While functional, this notation is not part of the ISO SQL standard. The recommended best practice in modern PostgreSQL versions is to utilize GENERATED AS IDENTITY columns. Identity columns are standard-compliant and offer superior management of underlying sequences and permissions compared to the older SERIAL implementation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2026-01-24 20:43 MIT License View
709 Table functions with OFFSET Find table functions that use OFFSET. OFFSET method is a common way for implementing pagination. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
710 Table has both state and status columns Find tables that contain both a state and a status column. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
711 Table has multiple columns for free-form descriptions Find tables that contain multiple columns for free-form textual descriptions. Make sure that the names of columns are understandable and sufficiently different. Make sure that there are no duplicate columns. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
712 Table, routine, and usage privileges that have been granted to a superuser Find table, routine, and usage privileges that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
713 Tables without columns Do not have in a database elements that are not useful. PostgreSQL permits tables with no columns. Such tables can be used to implement Boolean variables (tables TABLE_DEE and TABLE_DUM). On the other hand, such tables might be a result of database evolution, where developers have not noticed that they have dropped all the columns of a table or have not noticed that they have created such a table in the first place. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
714 Tables with the same name in different schemas Find tables with the same name in different schemas. Make sure that this is not a duplication. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
715 Textual code columns lacking specific pattern validation This query identifies semantic validation gaps in textual columns intended to store structured codes. It targets non-foreign key columns whose identifiers imply a specific format (e.g., containing the word "code"), but which lack adequate constraints to enforce that format. Specifically, it flags columns that have either no CHECK constraints at all, or only trivial constraints that prohibit empty/whitespace strings. Since "codes" typically adhere to a strict pattern (e.g., fixed length, specific character set), relying solely on a non-empty check is considered insufficient for data integrity. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-29 13:14 MIT License View
716 Textual columns that have a secondary index but the operator class for the column does not support pattern matching Find indexed textual columns where the indexing does not consider the possibility of pattern-based search. Such columns do not have an index where the used operator class makes the index suitable for use by queries involving pattern matching expressions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
717 TG_ARGV is missing Write correct code. If you pass arguments to a trigger function, then the function should use the arguments. TG_ARGV[]: "Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
718 The generator of surrogate key values can output the same value more than once Find surrogate keys where the generator can output the same value more than once. Key values must be unique, i.e., at some point the generator will prevent adding new rows to the table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
719 The longest names of database objects Find the TOP 3 longest (identifiers) names of user-defined objects. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
720 The longest names of database objects by object type "Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should be expressive. Find the TOP 3 longest (identifiers) names of user-defined objects by their type. These could be the first candidates of renaming in order to give to database objects better names. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View