PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 114 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
1 All columns of a base table have a default value Find base tables where all the columns have a default value. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
2 Base table column name is the same as its domain name Find base table columns that have the same name as the domain name or the data type name of the column. The names may have different uppercase/lowercase characters. Columns, domains, and types are different concepts in SQL and perhaps it is better to use different names in case of these. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
3 Base table column of sums of money has too big or small scale Find base table columns that name refers to the possibility that these are used to register data about prices/sums of money. Find the columns that have decimal type but have a too big (bigger than six) or a too small scale (zero). The selection of field size must be precise and should take into account the possible data in the column. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
4 Base table columns with the same name and type have different field sizes Find base table columns that have the same name and type but different field size. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
5 Base tables and foreign tables that do not have any CHECK constraints on non-foreign key columns Identify possibly missing CHECK constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
6 Base tables that have more than five indexes Find base tables that have more than five indexes. Indexes can be used to increase the speed of queries (SELECT statements). However, the amount of indexes shouldn't be too large. Otherwise it may reduce the speed of operations that are used to modify data. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
7 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
8 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
9 Column names that make joining tables more difficult This query identifies foreign key columns where the identifier differs from the referenced candidate key identifier. It explicitly excludes self-referencing constraints (recursive relationships), where name divergence is structurally mandatory. The primary objective is to identify opportunities to harmonize column names across the schema. Synchronizing the foreign key name with the referenced column name facilitates the use of the ANSI SQL USING clause in join operations (e.g., JOIN t1 USING (client_id)), which is significantly more concise than the explicit ON predicate required when names differ. Problem detection system catalog base tables only 2025-12-14 11:31 MIT License View
10 Columns of base tables that hold truth values but do not have a default value (Boolean columns) Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
11 Columns of base tables that hold truth values but do not have a default value (non-Boolean columns) Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It could be possible to select one of these as the default value in case of the columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
12 Columns that have the same name as their domain/type Find the columns that name is the same as the name of the type of the column or the domain of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
13 Cycles in relationships Find as to whether foreign key constraints between tables form a cycle. For instance, if table C refers to table B, table B refers to table A, table A refers to table C and all the foreign key columns are mandatory, then one cannot add data to the tables. Is cycle value in the result should not contain value TRUE. One should check manually as to whether the foreign key columns are mandatory or optional. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
14 Deferrable foreign key constraint with a RESTRICT compensating action Find deferrable foreign key constraint with ON UPDATE RESTRICT or ON DELETE RESTRICT compensating action. Referential actions are carried out before, and are not part of, the checking of a referential constraint. Deferring a referential constraint defers the checking of the of the constraint (a ) but does not defer the referential actions of the referential constraint. In PostgreSQL the essential difference between NO ACTION and RESTRICT is that NO ACTION allows the check to be deferred until later in the transaction, whereas RESTRICT does not. Thus RESTRICT could result with the failure of data modification where in case of NO ACTION the modification would succeed. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
15 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 2025-11-07 10:11 MIT License View
16 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 2025-11-07 10:11 MIT License View
17 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 2025-11-07 10:11 MIT License View
18 Different ways how to find default timestamp values Find all the default values of base table, view, and foreign table columns that are expressions invoking a function that returns a timestamp. Do it only if there are different expressions, i.e., there could be possible inconsistencies. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
19 Do not always depend on one's parent - column names are ot sufficiently different 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. Find only cases where the candidate key and foreign key column names are very similar (Levenshtein distance shorter than four). Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
20 Do not format comma-separated lists (based on user data) Find, based on the data that users have recoreded in a database, cases where a multi-valued attribute in a conceptual data model is implemented as a textual column of a base table. Expected values in the column are strings that contain attribute values, separated by commas or other separation characters. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View