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
361 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
362 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
363 Foreign keys with ON UPDATE CASCADE This query generates a list of all foreign key constraints that are defined with the ON UPDATE CASCADE action. This list must be manually audited to enforce the design principle that this action should be applied exclusively to relationships involving mutable, natural keys. Any instance found referencing an immutable surrogate key should be considered a design flaw and remediated. General system catalog base tables only 2025-11-08 10:40 MIT License View
364 Foreign servers without user mappings Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource." Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
365 Frequency of column name lengths based on the table type Find in case of base tables, materialized views, and views the number of columns based on the length of the column name. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
366 Frequency of lengths of the names of database objects "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 how many names (identifiers) of database objects there are with different lengths. The values could be used to compare different databases. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
367 Frequency of name components of database objects "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, what are the most popular components of the names (identifiers) of user-defined database objects, assuming, that the separator of the components is "_". Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
368 Frequency of table name lengths based on the table type This query provides a statistical analysis of identifier length across the schema. It calculates a frequency distribution by grouping base tables, views, and materialized views based on the character length of their names. The result is a count of how many objects exist for each distinct name length, which can be used to audit naming conventions or identify outliers. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-13 12:56 MIT License View
369 Frequent names of database objects "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 names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average. Also make sure that there is no duplication in play. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
370 Frequent 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 names (identifiers) of user-defined database objects that occur at least twice as frequently as a name occurs in average in case of the particular type of database objects. Also make sure that there is no duplication in play. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
371 Full text search columns that have no 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. These are the preferred index types for text search. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
372 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
373 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
374 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
375 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
376 Function Upper or Lower is used in an index on a non-textual column Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
377 Generated stored base table columns Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
378 Generated stored base table columns duplicates another column in the table Find generated stored columns in PostgreSQL base tables that duplicate other columns in the table. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
379 Generated stored base table columns that expression does not refer to any column Find generated stored base table columns that expression does not refer to any column of the table. It could be that there will be a constant value in every row in case of this column. The support of generated columns was added to PostgreSQL 12. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
380 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