Filter Queries

Found 7 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.

#1. Columns with tsvector type

INFORMATION_SCHEMA+system catalog base tables

Find columns of base tables and materialized views that have tsvector type.

General License: MIT (opens in new tab)

#2. Full text search columns that have no gin or gist index

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#3. Full text search columns with other type of index than gin or gist index

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#4. Inconsistent means to calculate tsvector values

INFORMATION_SCHEMA only

Find as to whether in the database there are multiple ways to calculate tsvector values, i.e., by using a generated column and by not using a generated column.

Problem detection License: MIT (opens in new tab)

#5. Multiple triggers that update tsvector values

system catalog base tables only

Find base tables that have multiple triggers to update tsvector values.

Problem detection License: MIT (opens in new tab)

#6. Triggers that are used to calculate tsvector values react to a wrong set of events

system catalog base tables only

Find triggers on base tables that are used to calculate tsvector values that react to a wrong set of events, i.e., react to the DELETE event or do not react to the INSERT and UPDATE events.

Problem detection License: MIT (opens in new tab)

#7. Tsvector values are not automatically calculated

INFORMATION_SCHEMA+system catalog base tables

Find base table columns with tsvector type in case of which it is not a generated column nor does the table has an associated trigger to calculate the tsvector value automatically.

Problem detection License: MIT (opens in new tab)
# Name (sorted ascending) Goal Type Data source Last update License Actions
1 Columns with tsvector type Find columns of base tables and materialized views that have tsvector type. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)
2 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 MIT (opens in new tab) View (opens in new tab)
3 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 MIT (opens in new tab) View (opens in new tab)
4 Inconsistent means to calculate tsvector values Find as to whether in the database there are multiple ways to calculate tsvector values, i.e., by using a generated column and by not using a generated column. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View (opens in new tab)
5 Multiple triggers that update tsvector values Find base tables that have multiple triggers to update tsvector values. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
6 Triggers that are used to calculate tsvector values react to a wrong set of events Find triggers on base tables that are used to calculate tsvector values that react to a wrong set of events, i.e., react to the DELETE event or do not react to the INSERT and UPDATE events. Problem detection system catalog base tables only MIT (opens in new tab) View (opens in new tab)
7 Tsvector values are not automatically calculated Find base table columns with tsvector type in case of which it is not a generated column nor does the table has an associated trigger to calculate the tsvector value automatically. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View (opens in new tab)