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
101 Inconsistent data type usage in case of registering a symbol Find whether the database uses both CHAR(1) and VARCHAR(1) columns to register a single symbol. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
102 Inconsistent data type usage in case of registering sums of money Find as to whether both the numeric type and integer types are used in case of columns that are meant for register data about prices. Problem detection INFORMATION_SCHEMA only 2021-03-21 18:32 MIT License View
103 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
104 Inconsistent means to calculate tsvector values Find as to whether in the database there are multiple ways to calculate tsvector values, i.e., by using a generated column and by not using a generated column. Problem detection INFORMATION_SCHEMA only 2023-11-07 11:35 MIT License View
105 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
106 Inconsistent time zone and precision usage in case of registering times Find as to whether different data types (with and without timezone) and precisions are used in case of registering times in different columns. Problem detection INFORMATION_SCHEMA only 2023-11-04 12:30 MIT License View
107 Inconsistent time zone and precision usage in case of registering timestamps Find as to whether different data types (with and without timezone) and precisions are used in case of registering timestamps in different columns. Problem detection INFORMATION_SCHEMA only 2023-11-04 12:28 MIT License View
108 Incorrect data type (based on default values) Find columns of base tables that have the default value CURRENT_USER or SESSION_USER but the data type is CHAR or TEXT. Problem detection INFORMATION_SCHEMA only 2023-12-30 11:06 MIT License View
109 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
110 Incorrect prevention of the empty string or strings that consist of only spaces in a field Find columns of base tables and foreign tables where the requirement that there should not be empty strings or strings that consist of only spaces in the column has been implemented incorrectly - by using the constraint trim(column_name) IS NOT NULL. PostgreSQL (differently from Oracle) does not replace the empty string with NULL. Empty string is a value but NULL is a special marker that denotes a missing value. Thus, in case of such constraint the DBMS checks a proposition '' IS NOT NULL. This is a true proposition and the DBMS does not prevent registration of such a row. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
111 Incorrect use of non-deterministic functions in CHECK constraints Do not create a constraint in case of which data that satisfied a constraint c at the registration time suddenly does not satisfy it any more as the time goes by. Find all the check constraints that use non-deterministic functions (now, current_timestamp, localtimestamp, current_date, current_time) in a way that makes this situation possible. Fort instance, localtimestamp(0)>end_date is an example of such constraint. Problem detection INFORMATION_SCHEMA only 2024-11-22 15:29 MIT License View
112 Insufficient number of user-defined base tables There must be at least n (seven in this case) user-defined base tables in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
113 Insufficient number of user-defined domains There must be at least n (one in this case) user-defined domains in the database each of that must be used in case of at least two columns of base tables. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
114 Insufficient number of user-defined foreign tables There must be at least n (two in this case) user-defined foreign tables in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
115 Insufficient number of user-defined views There must be at least n (four in this case) user-defined views in the database. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
116 IS DISTINCT FROM should be used instead of <> in WHEN clauses Use a right predicate in trigger condition in order to ensure that the trigger executes always when it has to but not more often. IS DISTINCT FROM treats NULL as if it was a known value, rather than unknown. It would be relevant if a column that is referenced in the action condition is optional, i.e., permits NULLs. Problem detection INFORMATION_SCHEMA only 2024-12-23 12:29 MIT License View
117 IS NULL check is probably not needed Find CHECK constraints that refer only to mandatory columns but the Boolean expression has IS NULL condition. Problem detection INFORMATION_SCHEMA only 2022-06-09 13:57 MIT License View
118 JSON type instead of JSONB type "In general, most applications should prefer to store JSON data as jsonb, unless there are quite specialized needs, such as legacy assumptions about ordering of object keys." (https://www.postgresql.org/docs/current/datatype-json.html) Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
119 Mixing different mechanisms to generate surrogate values Use the same mechanism of generating surrogate key values throughout the database. The use of SERIAL notation/explicitly creating a sequence generator and declaration of a column as an identity column will cause the creation of an external and internal sequence generator, respectively. Nevertheless, one should try to stick with using one of the mechanisms in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) Problem detection INFORMATION_SCHEMA only 2021-03-08 00:42 MIT License View
120 Mixing the use of TEXT and VARCHAR type in case of base table columns Declaring a column to have the type TEXT or the type VARCHAR (without the maximum number of characters) has the same end result in terms of what data can be recorded in the column. Nevertheless, one should try to stick with using one of the type names in order to cause less confusion. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) Problem detection INFORMATION_SCHEMA only 2024-12-14 13:41 MIT License View