PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 101 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 column DEFAULT values Find all the default values of base table, view, and foreign table columns. General INFORMATION_SCHEMA only 2022-10-31 10:18 MIT License View
2 All column dynamic DEFAULT values values that do not invoke a sequence Find all columns that have a dynamic default value, i.e., the value is returned by a function but the function is not for invoking a sequence. General INFORMATION_SCHEMA only 2021-11-15 15:57 MIT License View
3 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 2021-02-25 17:29 MIT License View
4 All column static DEFAULT values Find all columns that have a static default value, i.e., the value is not returned by a function. General INFORMATION_SCHEMA only 2021-11-15 15:57 MIT License View
5 All declaratively partitioned tables Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 15:13 MIT License View
6 All the non-primary key columns are optional Find base tables where all he non-primary key columns are optional. Avoid too many optional columns. You have to be extra careful with NULLs in case of formulating search conditions of data manipulation statements. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
7 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 2021-03-28 16:59 MIT License View
8 Base table columns with the same name have different types Find base table columns that have the same name but different type. In general, base tables columns that have the same name should have the same type as well. Problem detection INFORMATION_SCHEMA only 2023-01-14 20:54 MIT License View
9 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 2023-11-12 11:32 MIT License View
10 Candidate keys where all columns have a static default value Find base table primary key and unique constraints where all columns probably have a static default value. Candidate key columns (columns that belong to the primary key or to an unique constraints) shouldn't have static default values unless you are enforcing a rule that a table can have at most one row. The point of default values is that system assigns them automatically. There cannot be multiple rows with the same key value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-12 11:33 MIT License View
11 Cascading update is not needed (based on surrogate keys) Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-28 12:31 MIT License View
12 Chains of ON DELETE CASCADE Find all referential paths (chains of of parent-child tables that are associated through foreign key constraints) where all foreign key constraints have ON DELETE CASCADE compensating actions. Be careful with too long chains. General system catalog base tables only 2022-10-29 20:04 MIT License View
13 Column names that make joining more difficult (foreign key column name contains the table name) Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different but the difference comes from the fact that the foreign key column name starts or ends with the table name. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-12 10:26 MIT License View
14 Column names that make joining tables more difficult Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-08 13:56 MIT License View
15 Column names that make joining tables more difficult (quite similar names) Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is between two and four characters. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-08 13:56 MIT License View
16 Column names that make joining tables more difficult (very similar names) Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is one character. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-08 13:56 MIT License View
17 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 2023-11-09 13:14 MIT License View
18 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 2021-03-20 14:08 MIT License View
19 Columns of base tables that hold truth values that do not have a default value although they could have it (Boolean columns) Find columns of base tables that have type BOOLEAN. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column. Problem detection INFORMATION_SCHEMA only 2023-11-09 16:39 MIT License View
20 Columns of base tables that hold truth values that do not have a default value although they could have it (non-Boolean columns) Find columns of base tables that do not have type BOOLEAN but are used to record Boolean values. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 16:40 MIT License View