PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 16 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 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
3 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
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 2021-03-28 16:59 MIT License View
5 Different character maximum lengths that are used to define textual base table columns Find the number of different character maximum lengths that are used to define textual base table columns as well as list all the different lengths. Show also the total number of columns with char/varchar type. Maximum character length constrains values in a column. Thus, in case there is a small number of used lengths, it raises a question as to whether the lengths have been optimally selected. Sofware measure INFORMATION_SCHEMA only 2021-03-26 11:24 MIT License View
6 Double checking of the maximum character length Do not duplicate code. In this case a CHECK constraint duplicates the restriction that is already enforced with the help of the declaration of the maximum field size (for instance, VARCHAR(100)). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-18 13:27 MIT License View
7 Inappropriate field size or data type for column that strores database username Find columns of base tables that based on the default value of the column contain database username. However, the type of the column is not VARCHAR(63) or VARCHAR(128). Problem detection INFORMATION_SCHEMA only 2023-11-19 11:58 MIT License View
8 Inconsistent field sizes of columns for addresses Find as to whether columns for holding e-mail addresses, phone numbers, ip addresses, zip codes, ordinary addresses, or file addresses have inconsistent field sizes across tables. Problem detection INFORMATION_SCHEMA only 2023-11-01 12:53 MIT License View
9 Inconsistent precision and scale usage in case of registering sums of money Find as to whether different precisions/scales are used in case of registering data about sums of money in different columns. Problem detection INFORMATION_SCHEMA only 2021-03-29 13:07 MIT License View
10 Incorrect field size (based on default values) Find columns of base tables that have the default value CURRENT_USER or SESSION_USER but the field size is not 63 (default maximum identifier length in PostgreSQL). Problem detection INFORMATION_SCHEMA only 2023-12-30 11:05 MIT License View
11 Name and description maximum length Find tables where is both a column for registering name and description. Find the permitted maximum field size in these columns. Take into account that the maximum length may be controlled by using a CHECK constraint. Make sure that the permitted maximum field sizes are sufficiently different. General INFORMATION_SCHEMA+system catalog base tables 2021-02-24 20:36 MIT License View
12 Potentially a classifier is missing (based on field sizes) Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less. General INFORMATION_SCHEMA+system catalog base tables 2021-03-10 13:07 MIT License View
13 Potentially a classifier table is missing (based on field sizes) Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less and where the column name does not refer to names or comments. Return only data about these columns where there is no table with a similar name. Return data only about tables that could be referenced from more than one table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-18 20:08 MIT License View
14 Reasonable upper bound to the length of textual values is missing Find non-foreign key base table columns that are not used to record comments/descriptions/explanations etc. and that have TEXT or VARCHAR type without restrictions to the field size (field size in case of VARCHAR or a CHECK constraint). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-12-16 12:32 MIT License View
15 Reference to the numeric type is too imprecise, i.e., precision and scale are missing Find base table columns that have the DECIMAL/NUMERIC type, but do not have precision and scale specified. "Specifying: NUMERIC without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale." Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
16 The maximum number of characters may be missing Perhaps the character maximum length has been omitted accidentally, i.e., one wrote VARCHAR instead of VARCHAR(n) where n is the maximum permitted number of characters in the field value. VARCHAR and TEXT are synonyms. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View