PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1036 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
341 Find all non-foreign key columns of base tables Find all non-foreign key columns of base tables. Make sure that no foreign key constraint is missing. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
342 Find all publications Find publications of tables that have been created in order to enable logical replication. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
343 Find inconsistency in double underscore/space usage This query audits the database schema for inconsistency regarding the use of consecutive separators (double underscores or spaces) within identifiers. It groups objects by type (e.g., CHECK constraints, indexes) and identifies categories where a mixed naming convention exists—specifically, where some identifiers utilize consecutive separators (e.g., idx__name) while others of the same type do not (e.g., idx_name). This variation suggests a lack of enforced coding standards, leading to unpredictability in the schema. The query facilitates a review to establish and enforce a single, uniform naming convention. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-20 13:08 MIT License View
344 Find mixed usage of \w and [[:alnum:]] regex syntax This query audits regular expressions within the database to detect inconsistent syntax for matching alphanumeric characters. It checks for the concurrent use of both the Perl-style shorthand \w and the POSIX character class [[:alnum:]]. While these are often functionally similar, their exact behavior can differ based on locale settings (e.g., \w may include underscores while [[:alnum:]] does not). Using both styles within the same codebase indicates a lack of a clear standard, which can lead to maintainability issues and subtle, locale-dependent bugs. Standardizing on a single, well-understood syntax is recommended for clarity and predictability. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 11:11 MIT License View
345 Find || operations missing coalesce() protection This query identifies potential null-propagation defects in user-defined routines and views. It targets subqueries utilizing the standard concatenation operator (||) where operands are not protected by a coalesce() function. In PostgreSQL, the operation string || NULL yields NULL, causing the entire result to vanish if any component is missing. This behavior is often unintentional. The query flags these risky patterns, suggesting remediation via explicit null handling or the adoption of null-safe alternatives like concat(), concat_ws(), or format(). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-27 11:50 MIT License View
346 Find pointless regular expressions that match any value This query identifies regular expressions that are tautological, specifically those that effectively match any non-NULL string, such as ^.*$. It is superfluous because it evaluates to TRUE for any non-NULL value, including an empty string. It provides no actual data validation and acts as a no-operation (no-op) check. Such patterns are often artifacts of placeholder code, incomplete logic, or a fundamental misunderstanding of regular expression syntax. Removing them improves clarity and eliminates a useless computational step. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 11:16 MIT License View
347 Find useless coalesce, concat, or concat_ws calls with only one argument This query identifies superfluous function calls within routines and views, specifically targeting invocations of coalesce(), concat(), or concat_ws() that are supplied with only a single argument. These functions are variadic and designed to operate on multiple values (e.g., returning the first non-null value or joining strings). When called with a single argument, they function as an identity operation, returning the input unchanged. This pattern indicates either a coding error (missing arguments) or redundant logic that should be removed to simplify the expression. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-27 11:52 MIT License View
348 Find views that can accept data modification statements This query identifies all views against which data modification statements (INSERT, UPDATE, DELETE) can be executed without raising an error. It evaluates the is_insertable_into and is_updatable attributes to detect both "naturally" updatable views (where PostgreSQL automatically maps changes to base tables) and views made updatable via the rewrite rule system (specifically DO INSTEAD rules). Notably, this definition encompasses views defined with DO INSTEAD NOTHING rules; while such views do not physically modify data, they are technically considered updatable because they accept DML statements without failure. General INFORMATION_SCHEMA only 2025-12-13 12:34 MIT License View
349 Flag parameters A Boolean parameter may be used to determine what task to fulfill. In this case the routine has multiple tasks and does not satisfy the separation of concerns principle. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
350 Foreign key column has a default value that is not present in the parent table Find foreign key columns that have a default value that is not present in the parent table. Identify default values that cause violations of the referential constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
351 Foreign key column has a simple check constraint that is attached directly to the table Find foreign key columns that are covered by a simple check constraint, i.e., the constraint involves only one column. Look only constraints that are directly associated with the table, i.e., are not specified through a domain. Perhaps the constraint should be defined on the referenced candidate key column. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
352 Foreign key columns that are associated with a sequence generator Find foreign key columns that are associated with a sequence generator. Foreign key values are selected amongst the values that are registered as corresponding primary key/unique key values. Values in the foreign key columns are not directly generated by the system. These values might be system generated indirectly - generated when a row is added to the primary (parent) table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
353 Foreign key columns that do not have an integer or varchar type Find foreign key columns that do not have smallint, integer, bigint, or varchar(n) type. These are the most commonly used types in case of key/foreign key columns. Although the use of other types would be perfectly legal as well, make sure that you have selected the best possible data type for each and every column. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
354 Foreign key columns that have no index Find foreign key columns that do not have an index. Foreign key columns are often used for performing join operations. It is useful to index such columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
355 Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
356 Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint while the referenced table has the primary key Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns while at the same time the referenced table does have the primary key. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
357 Foreign key references a non-key (has optional columns) Find foreign key constraints that referenced column is optional. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
358 Foreign key references to a unique index columns not a unique key columns Find foreign key constraints that reference to the columns that are covered by a unique index not a unique key. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
359 Foreign key refers to a table that has at least one subtable in the inheritance hierarchy Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
360 Foreign keys with ON DELETE CASCADE This query identifies all foreign key constraints that use ON DELETE CASCADE for the purpose of a design audit. The results must be manually reviewed to verify that each use case correctly implements a specific conceptual relationship. According to design principles, ON DELETE CASCADE is only appropriate for implementing generalization hierarchies (is-a relationships), compositions (strong ownership), or the existential dependency of a non-main entity on a main entity. Any usage outside of these patterns is considered a potential design flaw. General system catalog base tables only 2025-11-08 10:51 MIT License View