PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 121 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 enumerated types Find all enumerated types. General INFORMATION_SCHEMA+system catalog base tables 2022-10-31 10:19 MIT License View
2 All system-defined TOAST-able types Find system-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). General system catalog base tables only 2020-11-06 14:51 MIT License View
3 All user-defined TOAST-able types Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). General system catalog base tables only 2020-11-06 14:51 MIT License View
4 A state machine is implemented with the help of an enumeration type Find implementations of state machines that uses an enumeration type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-26 15:39 MIT License View
5 At most one row is permitted in a table (based on enumeration types) Find base tables and foreign tables where based on the type of a column, a key constraint, and a NOT NULL constraint can be at most one row. Make sure that this is the real intent behind the constraint, not a mistake. Find tables where a column has an enumeration type with exactly one value, the column has NOT NULL constraint, and constitutes a key, i.e., has the PRIMARY KEY or UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2022-11-03 15:18 MIT License View
6 Base table column of comments/descriptions has an incorrect data type or maximum character length Find base table columns that name refers to the possibility that these are used to register comments/descriptions. Find the columns where the data type is not VARCHAR and TEXT or in case of VARCHAR the maximum number of permitted characters is smaller than 1000. In case of determining field sizes choose a size that permits registration of all possible legal values. Problem detection INFORMATION_SCHEMA only 2022-05-01 13:39 MIT License View
7 Base table column of measurements does not have a correct data type Find base table columns that name refers to the possibility that these are used to register measurement results. Find the columns that do not have an integer or numeric type. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
8 Base table column of national identification numbers does not have a correct data type Find non-textual base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). The codes can contain additional symbols to numbers. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
9 Base table column of national identification numbers has a too short field size Find base table columns with VARCHAR type that name refers to the possibility that these are used to register national identification numbers (personal codes). Find the columns where the field size is shorter than 20. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
10 Base table column of quantities does not have a numeric type (based on column names) Find base table columns that name refers to the possibility that these are used to register quantities of things. Find the columns that do not have a numeric type. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
11 Base table column of sums of money does not have a numeric type (based on column names) Find base table columns that name refers to the possibility that these are used to register prices/sums of money. Find columns that do not have a numeric type. Problem detection INFORMATION_SCHEMA only 2021-03-21 11:45 MIT License View
12 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 2021-03-21 11:45 MIT License View
13 Base table column of surrogate key values does not have an integer data type (based on column names) Find base table columns that belong to a primary key, unique, or foreign key constraint and that name refers to the possibility that these are used to hold surrogate key values. Find the columns where the data type of the column is not an integer type or uuid. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-13 12:10 MIT License View
14 Base table columns for recording geographic coordinates (based on column names) Find base table columns that are according to the name meant for recording coordinates. General INFORMATION_SCHEMA only 2021-10-08 11:59 MIT License View
15 Base table columns for recording geographic coordinates that do not have a suitable type (based on column names) Find base table columns that are according to the name meant for recording geographic coordinate but do not have a suitable type (numeric or point). Problem detection INFORMATION_SCHEMA only 2021-10-08 12:01 MIT License View
16 Base table columns permitting empty strings and strings that consist of only whitespace characters Find non-foreign key columns of base tables that have a textual type and do not have any simple CHECK constraint, i.e., a constraint that involves only one column. Such columns can contain the empty string and strings that consist of only whitespace. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
17 Base table columns permitting temporal values that may be outside the range of logical values Find base tables columns with temporal types (date and timestamp) that do not belong to a foreign key and that do not have any associated simple CHECK constraints, i.e., constraint that involves only one column. For instance, in the column registration_time that does not have any associated CHECK constraints could be values '1200-01-01 00:00' or '5900-12-31 00:00'. Rows with these values most probably represent wrong propositions and the system should restrict registration of such data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-17 00:40 MIT License View
18 Base table columns where TOAST-ing strategy has been changed to plain Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed to plain. It means that potentially, if a value in the column is large enough, it is not possible to save the row. Problem detection system catalog base tables only 2021-02-25 17:29 MIT License View
19 Base table columns with a composite type Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table. General system catalog base tables only 2020-11-06 14:51 MIT License View
20 Base table columns with an array type Find base table columns with an array type. Think through as to whether a column with an array type could be replaced with a separate table. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View