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
161 Perhaps the type of a base table column should be BOOLEAN (based on column names) Find base table columns that based on the name seem to hold truth values. Find columns that name starts with "is_" or "has_" or "can_" or "on_" and that do not have Boolean type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-03 09:41 MIT License View
162 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
163 More than one index on a column Find base table columns that belong to more than one index (including automatically created indexes that support constraints). General system catalog base tables only 2021-11-10 14:44 MIT License View
164 Columns with only one value Find base table columns that contain only one value. Perhaps it is an unnecessary column. Having only one value is most likely inadequate for testing. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
165 Perhaps the column type should be UUID Find base table columns that do not have uuid type but the name of the column refers to the possibility that the values in the column are uuid's. Problem detection INFORMATION_SCHEMA only 2022-06-09 15:07 MIT License View
166 Perhaps the type of a base table column should be BOOLEAN (based on enumerated types) Find base table columns that have an enumerated type that seems to emulate Boolean type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
167 Boolean column for gender Find base table columns that have Boolean type and based on the column name are meant for recording data about gender. Problem detection INFORMATION_SCHEMA only 2022-04-18 00:57 MIT License View
168 Three-valued logic (Boolean columns) Find base table columns that have Boolean type and do not have NOT NULL constraint. Use two-valued logic (TRUE, FALSE) instead of three-valued logic (TRUE, FALSE, UNKNOWN). Because NULL in a Boolean column means unknown make all the Boolean columns mandatory. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
169 Duplicate DEFAULT values of base table columns Find base table columns that have both default value determined through a domain and default value that is directly attached to the column. Do not duplicate specifications of default values to avoid confusion and surprises. If column and domain both have a default value, then in case of inserting data the default value that is associated directly with the column is used. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
170 Perhaps the type of a base table column/domain should be VARCHAR (based on column names) Find base table columns that have CHAR type, where character maximum length is bigger than 1 and the name of the column does not refer to the possibility that the column holds some kind of codes or flags or hash values. Problem detection INFORMATION_SCHEMA only 2023-11-12 10:48 MIT License View
171 Do not use FLOAT Data Type Find base table columns that have FLOAT, REAL, or DOUBLE PRECISION type. "The data types real and double precision are inexact, variable-precision numeric types. On all currently supported platforms, these types are implementations of IEEE Standard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it." (PostgreSQL documentation) Do not use the approximate numeric types FLOAT, REAL, and DOUBLE PRECISION in order to present fractional numeric data. Due to the use of the IEEE 754 standard the results of calculations with the values, which have one of these types, can be inexact because out of necessity some numbers must be rounded to a value, which is very close. "Comparing two floating-point values for equality might not always work as expected." (PostgreSQL documentation) Problem detection INFORMATION_SCHEMA only 2021-03-12 15:41 MIT License View
172 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
173 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
174 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
175 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
176 Unnecessary usage of the numeric type in case of base table columns Find base table columns that have type NUMERIC and the scale is 0, i.e., one can record in the column only integer values. Arithmetic operations are slower in case of the numeric type compared to an integer type. Thus, in order to record integer values, one should use columns with the type SMALLINT, INTEGER, or BIGINT instead of NUMERIC(p,0). Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
177 Perhaps spaces are unnecessarily restricted Find base table columns that name refers to the possibility that the column is used to record names or textual descriptions but the column seems to have a simple check constraint that restricts spaces in these. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-18 13:30 MIT License View
178 Perhaps the type of a base table column/domain should be temporal (deadlines) Find base table columns that name refers to the possibility that there are registered deadlines but the column does not have a temporal type. Problem detection INFORMATION_SCHEMA only 2021-03-21 17:00 MIT License View
179 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
180 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