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
221 Definition of a non-minimal superkey instead of a candidate key (based on unique indexes) Find pairs of non-partial unique indexes where the columns of a index are a proper subset of the columns of another index. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Exclude the pairs where both participants have been created to support a constraint. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define keys based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Defining a unique index essentially means defining a key in the table but it is done at the lower level of abstraction. Problem detection system catalog base tables only 2022-10-21 15:56 MIT License View
222 Delimited identifiers Delimited identifiers (quoted identifiers) are case sensitive. Identifiers of database objects should be case insensitive in order to simplify their management. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:21 MIT License View
223 Depth of referential tree of a schema Depth of referential tree of a database schema is the longest referential path between the tables in this schema (Piattini et al., 2001). In other words, it is the biggest value among the DRT(T) values of all the tables of the schema. Sofware measure system catalog base tables only 2020-11-14 15:28 MIT License View
224 Depth of relational tree of a table Depth of relational tree of a table T (DRT(T)) is defined by Piattini et al. (2001) as "the longest referential path between tables, from the table T to any other table in the schema". The result may help to classify the data. If the depth is 0, then probably the table contains classifers. Tables with the largest depth probably contain some extra information about main entities. Sofware measure system catalog base tables only 2020-11-14 16:13 MIT License View
225 Derived table names have prefix or suffix Find the names of views and materialized views that have prefix or suffix. Follow the same naming style as in case of base tables (derived tables are also tables). Thus, if base tables do not have prefixes or suffixes, then derived tables shouldn't have these as well. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
226 Derived table on top of another derived table Do not build multiple levels of derived tables (views and materialized views) because it will hamper evolvability and understandability of the tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
227 Derived table presents the same data in the same way as a single base table Find derived tables (views and materialized views) that present data from one base table without adding columns, renaming columns, changing the order of columns, removing columns, or restricting rows. Perhaps something is missing from the subquery of the derived table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-25 12:15 MIT License View
228 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
229 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
230 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
231 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
232 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
233 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
234 Derived table uses a function to get data from another table Find views that use a function to get data from another table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 14:36 MIT License View
235 Deterministic (immutable) functions that do not have input parameters Find deterministic functions that do not have any input parameters. Make sure that it is correct because in general a deterministic function must calculate a value based on input. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 19:53 MIT License View
236 Deterministic (immutable) functions that do not return a value Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-20 19:33 MIT License View
237 Different character maximum lengths that are used to define textual base table columns Find the number of different character maximum lengths that are used to define textual base table columns as well as list all the different lengths. Show also the total number of columns with char/varchar type. Maximum character length constrains values in a column. Thus, in case there is a small number of used lengths, it raises a question as to whether the lengths have been optimally selected. Sofware measure INFORMATION_SCHEMA only 2021-03-26 11:24 MIT License View
238 Different data types that are used to define base table columns Find the number of different data types that are used to define base table columns as well as list all the different types. Data type constrains values in a column. Thus, in case there is a small number of used types, it raises a question as to whether the types have been optimally selected. Sofware measure INFORMATION_SCHEMA only 2020-11-27 10:40 MIT License View
239 Different foreign key column names in case of referencing the same candidate key Find the cases when the names of columns in different foreign keys that reference to the same candidate key are different. If different names reflect different roles, then it is legitimate. However, there could also be accidental differences that makes it more difficult to use the database. Problem detection system catalog base tables only 2021-03-12 11:21 MIT License View
240 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