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
1 Address field size is incorrect (too short or too long) Find base table columns that are meant for recording different types of addresses where the filed size does not take into account the possible maximum length. Problem detection INFORMATION_SCHEMA only 2023-11-09 12:55 MIT License View
2 All CHECK constraints of domains that are not associated with any table Find all CHECK constraints (except NOT NULL) of domains that are not associated with any column. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
3 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
4 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
5 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
6 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
7 All domain default values Find domains that specify a default values and columns that are defined based on the domain. Make sure that there are no unsuitable default values. General INFORMATION_SCHEMA only 2021-01-19 13:04 MIT License View
8 All domains Find all domains. General INFORMATION_SCHEMA only 2020-11-10 11:49 MIT License View
9 All foreign tables Find all foreign tables. General INFORMATION_SCHEMA only 2020-11-06 15:20 MIT License View
10 All table CHECK constraints that cover at leat one column Find all CHECK constraints (except NOT NULL) that are associated with a base table or a foreign table column. It is useful to enforce as many constraints at database level as possible. In this way one improves data quality as well as gives extra information to the database users (including the DBMS engines, development environments, and applications). General INFORMATION_SCHEMA only 2024-01-01 11:27 MIT License View
11 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
12 All updatable views Find all views through which it is possible to modify data in base tables. Is_insertable_into and is_updatable show as to whether the view is naturally updatable in PostgreSQL or has associated rules that make sure that INSERT/UPDATE/DELETE operations against the view will not cause an error. Please note that the rule could be DO INSTEAD NOTHING rule, i.e., data modification through the view does not cause an error but the data is not actually modified. If the updatability is achieved due to rules, then is_updatable=YES if the view has both UPDATE and DELETE rule that make sure that UPDATE and DELETE operations do not cause an error. General INFORMATION_SCHEMA only 2024-01-14 17:09 MIT License View
13 All user schemas Find all the schemas in the database that are not used for the system purposes. General INFORMATION_SCHEMA only 2022-11-04 15:14 MIT License View
14 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 2021-02-25 17:29 MIT License View
15 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
16 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
17 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
18 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
19 Base table column of personal names has questionable properties Find base table columns that name refers to the possibility that these are used to register personal names. Although there are very long personal names the general approach is to register a shortened version of these. Thus, a large field size is not a good idea because it would cause usability and security problems. There are persons who only have one name (mononymous persons). Database design must take it into account. Problem detection INFORMATION_SCHEMA only 2022-10-29 20:35 MIT License View
20 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