PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 997 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
101 Constraints that are not redefined in a subtable but there is a CHECK constraint that compensates this Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) but not in its subtable. Exclude constraints where in case of the subtable there is a CHECK that permits only one specific value in the constraint column. The presence of such check would make the design acceptable. Unfortunately, PostgreSQL table inheritance is implemented in a manner that some constraints (CHECK, NOT NULL) are inherited from the supertable but others are not. "All check constraints and not-null constraints on a parent table are automatically inherited by its children, unless explicitly specified otherwise with NO INHERIT clauses. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited." (PostgreSQL documentation) General INFORMATION_SCHEMA+system catalog base tables 2020-12-25 16:20 MIT License View
102 Constraints that are redefined in a subtable. Find primary key, unique, foreign key, and exclude constraints that have been defined in a supertable (parent table) and have been redefined in its subtable. General system catalog base tables only 2020-12-02 17:28 MIT License View
103 Constraint-supporting UNIQUE indexes with the same leading column Find indexes that support a uniqueness constraint and have the same leading column. General system catalog base tables only 2023-11-26 16:01 MIT License View
104 Coverage by derived tables Find for each base table the list of derived tables (both views and materialized views) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from the subquery of at least one derived table. General INFORMATION_SCHEMA+system catalog base tables 2023-11-11 09:31 MIT License View
105 Coverage by routines that have the SQL-standard body Find for each base table the list of routines (functions and procedures) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from at least one routine. General INFORMATION_SCHEMA+system catalog base tables 2021-11-05 17:00 MIT License View
106 Data type usage in the base table columns Get overview of used data types in the columns of base tables. If the selection is very small then this is a warning sign that perhaps unsuitable types have been used. General INFORMATION_SCHEMA only 2023-12-22 12:39 MIT License View
107 Deferrable constraints Find all deferrable constraints. General system catalog base tables only 2021-10-08 11:25 MIT License View
108 Derived tables that have a column with the xid type Find the derived tables (views and materialized views) that have a column with the xid type, i.e., these use the data from the hidden xmin column of a base table. If one uses optimistic approach for dealing with the concurrent data modifications, then xmin values should be presented by views and used in routines that modify or delete rows. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
109 Derived tables that present data in json or xml format Find views and materialized views that present data in json or xml format. Instead of recording data in this format in base tables one could generate the data value on the fly based on data that has been recorded in base tables. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
110 Derived tables with multiple DISTINCT's Find derived tables (views and materialized views) that contain more than one DISTINCT invocation. Make sure that the query is correctly written, including that it does not have unwanted formation of a Cartesian product. General INFORMATION_SCHEMA+system catalog base tables 2020-12-10 13:25 MIT License View
111 Derived tables with ranking Find views and materialized views that use rank and dense_rank window functions. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
112 Derived tables with sorting Find derived tables where the rows are ordered, i.e., there is ORDER BY clause at the end of the view. Different users may want to see the rows in different order. Thus, the DBMS may have to do extra and unnecessary work by firstly sorting based on one set of rows and after that based on other set of rows. Use sorting if you know that all the users want the rows in the same order and cannot/will not change the order in the application. General INFORMATION_SCHEMA+system catalog base tables 2020-12-22 21:34 MIT License View
113 Derived tables with string_agg Find views and materialized views that use string_agg aggregate function. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
114 Different non-surrogate key default values Find the different default values that implement something other than a surrogate key. General INFORMATION_SCHEMA only 2021-03-04 10:31 MIT License View
115 Different search paths of SECURITY DEFINER functions Find the different search paths used in case of SECURITY DEFINER functions and the number of their occurrences. Make sure that these have been specified correctly and consistently and that they do not refer to any non-existent schemas. General INFORMATION_SCHEMA+system catalog base tables 2020-12-16 10:59 MIT License View
116 Different tasks of rules Find different tasks that are solved by using rules, i.e., different rules on the same table or different tables that do the same thing are considered to solve one task. General system catalog base tables only 2024-01-14 16:10 MIT License View
117 Different tasks of triggers Find different tasks that are solved by using triggers, i.e., different triggers on the same table or different tables that do the same thing are considered to solve one task. General INFORMATION_SCHEMA+system catalog base tables 2024-01-04 00:53 MIT License View
118 Domain candidates Find column descriptions that are candidates for describing a domain. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
119 Domain usage in base tables Find for each domain the number of usages in base tables. The less you have managed to reuse domains in case of different columns, the more you have wasted your time by creating the domains. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
120 Do not always depend on one's parent Find where a hierarchical structure is implemented in a base table by adding a foreign key that refers to a candidate key of the same table. General system catalog base tables only 2021-03-12 15:36 MIT License View