PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1018 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 Base tables that have neither a unique constraint nor the primary key Find base tables without any unique constraints and the primary key. In such tables there are no restrictions for recording duplicate rows. Each row represents a true proposition about the real world. It does not make the proposition truer if one presents it more than once. Moreover, duplicate rows increase data size. Without keys the DBMS lacks vital information about data in the database that it can internally use to choose better execution plans and in this way improve performance of database operations. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
102 Base tables that have no uniqueness requirement for rows whatsoever Find base tables without any unique constraints and primary key as well as any unique index, whether it is created explicitly by a developer or automatically by the DBMS. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
103 Base tables that have only the surrogate key and do not have any other column Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
104 Base tables where all the columns are optional Find base tables where all the columns are optional, i.e., permit NULLs. In such tables can be rows with no identity value and thus indistinguishable from other rows. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
105 Base tables where all the unique columns are optional Find the base tables where all the unique columns are optional. In such tables there can be rows without values that identify these rows. In this case there can be rows in the table where the values that should identify the row are missing. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
106 Base tables where certainly registration time is not recorded Find base tables that do not have any column with a timestamp type. In such tables certainly registration time is not recorded. Make sure as to whether recording registration time is necessary. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
107 Base tables where uniqueness is achieved by using only unique indexes Find base tables where uniqueness is achieved by using only unique indexes, i.e., there is at least one unique index but no uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
108 Base tables, which statistics is probably not up to date Find base tables where statistics has not been collected at all or it has been lastly collected more than 40 days ago. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
109 Base tables with exactly one key Find all base tables that have exactly one PRIMARY KEY or UNIQUE constraint. Find and enforce all the keys. Are you sure there are not more keys in the table? General system catalog base tables only 2025-11-07 10:11 MIT License View
110 Base tables with multiple Boolean columns Find base tables that have more than one column with Boolean type. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
111 Base tables with multiple comment columns Find base tables with more than one comment columns General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
112 Base tables with multiple name columns Find base tables with more than one name columns. Perhaps the normalization level of the table is low. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
113 Base tables with multiple temporal columns Find base tables that have more than one column with a temporal type (date or timestamp). General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
114 Base tables with plenty of data Find base tables that have 1000 rows or more. General system catalog base tables only 2025-11-07 10:11 MIT License View
115 Base tables with the biggest number of rows Find the base tables that belong to the top 5 in terms of the number of rows in the table. There should be test data in the tables. General system catalog base tables only 2025-11-07 10:11 MIT License View
116 BOOLEAN base table and foreign table columns with a CHECK constraint that involves olnly this column Find base table and foreign table columns with the Boolean type that has a CHECK constraint that involves only this column. Avoid unnecessary CHECK constraints. The Boolean type contains only two values and there is nothing to check. By creating a check that determines that possible values in the column are TRUE and FALSE, one duplicates the attribute constraint (column has a type). This is a form of duplication. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
117 BOOLEAN base table and foreign table columns with a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves olnly this column Find base table columns with the Boolean type that has a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves only this column. Avoid unnecessary constraints. It is quite improbable that there must be such constraints. For instance, a table with PRIMARY KEY () or UNIQUE () constraint can have at most two rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
118 Boolean column for gender Find base table columns that have Boolean type and based on the column name are meant for recording data about gender. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
119 B-tree index fillfactor has been explicitly set to 90 This query identifies B-tree indexes where the FILLFACTOR has been explicitly set to 90. Since 90 is the default FILLFACTOR for B-tree indexes in PostgreSQL, this explicit declaration is superfluous. Removing such redundant settings simplifies the schema definition, improves maintainability, and makes intentionally non-default configurations more apparent. Problem detection system catalog base tables only 2025-11-10 09:15 MIT License View
120 Candidate key columns that have a static default value Find base table columns that are covered by a primary key or a unique constraint and that probably have a static default value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View