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
401 Full text search columns with other type of index than gin or gist index Find columns of base tabels and materialized views with the type tsvector that do not have a gin or a gist index but have another type of index (e.g., b-tree). Gin and Gist are the preferred index types for text search. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
402 Function-based index usage mismatch in derived tables This query identifies performance inefficiencies caused by a semantic mismatch between function-based index definitions and their usage within derived tables (views, materialized views). PostgreSQL's query optimizer generally requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but accessed in a view using a different function (e.g., lower(col)). In such cases, the optimizer cannot utilize the pre-calculated index, resulting in suboptimal execution plans (typically sequential scans). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-22 18:32 MIT License View
403 Function-based index usage mismatch in user-defined routines This query identifies performance inefficiencies within user-defined routines caused by a mismatch between the logic in the routine and the definition of function-based indexes. PostgreSQL's query optimizer requires the expression in a query to strictly match the expression defined in the index to trigger an index scan. This query flags instances where a column is indexed using one function (e.g., upper(col)) but is accessed in a routine using a different function (e.g., lower(col)). Consequently, the optimizer fails to utilize the pre-calculated index, forcing a costly sequential scan. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-22 18:42 MIT License View
404 Functions that have transactional control This query identifies user-defined functions that contain restricted Transaction Control Language (TCL) statements, such as BEGIN, COMMIT, ROLLBACK, or SAVEPOINT. In PostgreSQL, functions execute within the context of the calling query's transaction and are strictly prohibited from managing transaction boundaries. Attempting to execute these commands within a function body results in a runtime error. To implement transactional logic (such as committing data in batches), the code must be refactored into a PROCEDURE, which supports transaction management. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-14 12:25 MIT License View
405 Grantable column privileges Find column privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
406 Grantable routine privileges Find routine privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
407 Grantable table privileges Find table privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
408 Grantable usage privileges Find usage privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
409 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
410 Gratuitous context in the names of parameters This query enforces a concise coding style by checking the names of parameters within routines (such as functions or procedures). It finds parameters whose names unnecessarily repeat the name of the routine they belong to. For example, in a function named calculate_invoice, a parameter named calculate_invoice_id would be flagged, as invoice_id is sufficient. A routine cannot have two parameters with the same name, so the shorter name is unambiguous within the context of the routine and results in cleaner, more readable code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:12 MIT License View
411 Identifiers that explicitly say that they carry no meaning Find identifiers that explicitly say that they carry no meaning. Such identifier is called "unnamed" or "anonymous". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
412 Inappropriate use of trim function in whitespace constraints This query identifies a semantic mismatch between the name and implementation of CHECK constraints (on tables, foreign tables, or domains). It targets constraints whose names suggest they validate against whitespace-only strings (e.g., names containing 'whitespace', 'space', 'blank'), but whose logic inappropriately uses the trim() function. The trim() function is a formatting tool for removing leading/trailing spaces, not a validation tool for ensuring a string is not composed entirely of whitespace. This indicates a likely implementation error, as a more robust regular expression (e.g., column !~ '^\s*$') is the correct tool for this type of validation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 12:34 MIT License View
413 Inconsistency of using column data types/field sizes in case of columns that implement relationships Find foreign key constraints where the candidate key columns (belong to a PRIMARY KEY/UNIQUE constraint) and foreign key columns do not have the same data type and field size. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
414 Inconsistency of using parameter data types Find parameters of routines that have the same name but a different type. Parameters that have the same name should have, in general, the same data type as well, assuming that the routines, which have the parameters, have different names, i.e., there is no overloading in play. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
415 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
416 Inconsistent CHECK constraints on columns with the same name This query identifies inconsistencies in data validation for columns that share the same name across different tables. It flags cases where a conceptual data element (e.g., 'email', 'postal_code') is subject to a CHECK constraint in some tables but lacks one in others. This violates the principle of uniform data integrity, creates semantic ambiguity, and can allow invalid data to enter the system through the unconstrained columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 09:53 MIT License View
417 Inconsistent digit character class syntax in regular expressions This query audits regular expressions within the database to detect syntactical inconsistencies in identifying numeric digits. It checks for the concurrent use of disparate character class notations: range-based ([0-9]), Perl-style shorthand (\d), and POSIX character classes ([[:digit:]]). While these are often functionally equivalent for standard ASCII digits, mixing multiple syntaxes within a single codebase indicates a lack of standardization, which reduces code readability and increases cognitive load during maintenance. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-25 17:13 MIT License View
418 Inconsistent names of database objects that are used to manage the state of main objects in the database "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) The naming must be consistent. One should avoid mixing synonyms like "seisund", "staatus", and "olek" in Estonian or "state" and "status" in English and stick with one term. For instance, it is a bad practice to use word "state" in table names but word "status" in function names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
419 Inconsistent naming of columns with BOOLEAN type This query audits the naming conventions of BOOLEAN columns to detect inconsistency. It checks whether the database contains a mix of styles: some boolean columns adhering to a semantic prefix convention (e.g., is_active, has_permission) and others using unprefixed nouns or adjectives (e.g., active, permission). The presence of both styles indicates a lack of a consistent standard, which can reduce schema clarity and predictability for developers. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:50 MIT License View
420 Inconsistent naming of comment columns Find columns of tables that start with the word comment or komment but end differently (excluding numbers). Return result only if there is more than one naming variant of such columns in the database. For instance, a column has the name "comment" but another "comments". Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View