PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 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
141 Perhaps incorrect WHEN clause Find row level triggers that have action condition (WHEN clause) but the Boolean expression in its specifications does not refer to neither NEW nor OLD variable. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
142 Perhaps 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. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
143 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 2025-11-07 10:11 MIT License View
144 Perhaps the precision in case of a base table column with NUMERIC/DECIMAL type is too small Make sure that in case of using the type DECIMAL/NUMERIC as the type of a base table column the precision (the permitted number of digits in the number) is not too small. For instance, the biggest value in the type NUMERIC(1,1) is 0.9. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
145 Perhaps the type of a base table column/domain should be BOOLEAN (based on types and default values) Find base table columns and domains that have a textual type and the default value that represents a truth-value. For instance, the type of a column could be VARCHAR and the column has the default value 'TRUE'. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
146 Perhaps the type of a base table column/domain should be INTEGER/SMALLINT/BIGINT (based on sequence generators) Specify for each column/domain a right data type that takes into account expected values in the column/domain. Find base table columns and domains that refer to the nextval function by using the default value mechanism but do not have the type INTEGER, SMALLINT, or BIGINT. This check is performed in case of identity columns: ERROR: identity column type must be smallint, integer, or bigint. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
147 Perhaps the type of a base table column/domain should be numeric (based on default values) Specify for each column/domain a right data type that takes into account expected values in the column/domain. Find base table columns and domains that have a textual type but the default value that represents a number (for instance, '100', '2', or '0.22'). Exclude columns about formats. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
148 Perhaps the type of a base table column/domain should be SMALLINT (based on classifiers) Find columns that name points to the possibility that values in this are classifier codes. The column has a numeric type but it is not SMALLINT. Usually each classifier type has so few values that type SMALLINT would be appropriate. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
149 Perhaps the type of a base table column/domain should be temporal (based on default values) Find base table columns and domains that have a textual type but the default value that represents a temporal value (either a static value or invocation of a function that returns such value). Specify for each column/domain a right data type that takes into account expected values in the column/domain. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
150 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 2025-11-07 10:11 MIT License View
151 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 2025-11-07 10:11 MIT License View
152 Perhaps the type of a base table column should be an integer type (based on column names) Find columns of base tables where the name of the column has prefix or suffix "id" or has the name "id" but the column does not have an integer type or uuid type. A convention is to use the phrase "id" in the names of surrogate key columns. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
153 Perhaps the type of a base table column should be TIMESTAMPTZ This query identifies all base table columns defined with the timestamp without time zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones. Problem detection INFORMATION_SCHEMA only 2025-11-07 12:21 MIT License View
154 Perhaps the type of a base table column should be TSTZRANGE This query identifies all base table columns defined with the range of timestamp without zone data type. Use of this data type is a potential design flaw as it can lead to ambiguity and bugs when handling data from multiple time zones. Problem detection INFORMATION_SCHEMA only 2025-11-07 12:24 MIT License View
155 Perhaps the type of a base table column should be XML, JSON, or JSONB (based on column names) Find base table columns that name refers to the possibility that these are used to register XML/JSON values. Find the columns that do not have an appropriate data type (xml, json, jsonb). One shouldn't use columns with a textual type to register such data. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
156 Perhaps too many subconditions in a CHECK constraint Find check constraints of base table and foreign table columns that are either associated with more than one column and have at least one AND operation or are associated with exactly one column and have two or more AND operations. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
157 Permitting in a column only empty strings and strings that consist of whitespace characters This query identifies logically flawed CHECK constraints on table columns. It specifically searches for constraints that are defined in such a way that they only permit the insertion of an empty string or strings composed entirely of whitespace characters. This behavior is invariably a bug, likely a typo in the constraint's logic (e.g., using ~ instead of !~), which effectively renders the column unusable for storing meaningful data. Problem detection INFORMATION_SCHEMA only 2025-12-29 12:03 MIT License View
158 Potential duplication of sequence generators Do not create unnecessary sequence generators. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
159 Potentially missing default values of base table columns Find columns of base tables without a default value that are either Boolean columns that based on the name seem to implement a state machine or temporal columns that based on the name seem to keep registration or update time. These columns often have a default value. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
160 Potentially unused sequence generators Find sequence generators that are not associated with any column through the default value mechanism. Please note, that it is also possible to refer to a sequence generator from a routine or from an application. If these are indeed not used, then these should be dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View