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
181 Inconsistency between the name and the type of a base table column (dates) Find base table columns that name refers to the possibility that these are used to register dates. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column data type is "date", then the suffix of the column name should be "kp" (Estonian) or "date" (English). Problem detection INFORMATION_SCHEMA only 2021-03-27 16:16 MIT License View
182 Columns of base tables with data about postal addresses, file addresses, or web addresses that have an incorrect data type Find base table columns that name refers to the possibility that these are used to register file/web addresses. Find the columns where the type refers to the possibility that values in the column are actual files. Problem detection INFORMATION_SCHEMA only 2021-03-27 13:46 MIT License View
183 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
184 Field size is not sufficiently different in case of base table columns Find base table columns that name refers to the possibility that these are used to register names. Find base table columns that name refers to the possibility that these are used to register comments/descriptions/etc. Find the cases where a base table contains columns from the both sets and the field size in case of the latter is not at least twice as big as in case of the former. For example, if the name of a service can be 100 character long, then it is quite illogical that the description of the service is only at most 200 characters long. Problem detection INFORMATION_SCHEMA only 2021-03-27 18:51 MIT License View
185 Base table has a national identification number as a key Find base table columns that name refers to the possibility that these are used to register national identification numbers (personal codes). Find the columns that constitute a key of a table. This in turn enforces a (restrictive) business rule that all persons of interest come from one country. Make sure that the enforced constraint is valid, i.e., there is indeed such rule in the domain. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
186 Password should not be open text Find base table columns that name refers to the possibility that these are used to register passwords. Find the columns that have a CHECK constraint that seems to determine the minimal or maximal permitted length of the values in the column. Passwords in a database table must be hashed and salted. Checking the strength of the password by using a check constraint is in this case impossible and the check constraints that try to do it should be removed from the database. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
187 Are the passwords hashed? Find base table columns that name refers to the possibility that these are used to register passwords. Return a value from each such column. Make sure that the password is not registered as open text. General INFORMATION_SCHEMA+system catalog base tables 2020-11-10 12:14 MIT License View
188 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
189 Base table column of sums of money 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 prices/sums of money. Find columns that do not have a numeric type. Problem detection INFORMATION_SCHEMA only 2021-03-21 11:45 MIT License View
190 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
191 Inconsistency between the name and the type of a base table column (timestamps) Find base table columns that name refers to the possibility that these are used to register timestamps. Find the columns that do not have an appropriate data type. Column names should reflect the data that is possible to record in the column. For instance, in case of temporal data the column name should indicate as to whether we record dates or timestamps. If the column type is "timestamp", then the suffix of the column name should be "aeg" (Estonian) or "time" (English). Problem detection INFORMATION_SCHEMA only 2021-03-28 15:12 MIT License View
192 Base table columns for storing username, password, and salt Find base table columns that name refers to the possibility that these are used to register usernames, passwords, and salt. Password should not be open text. It should be a hash value that has been hashed by using salt. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
193 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 2021-03-28 15:30 MIT License View
194 Using an internal data type - name Find base table columns that use type name that is used in system catalog tables. It is not a problem if the column is meant for recording identifiers of database objects. General INFORMATION_SCHEMA only 2021-03-30 13:36 MIT License View
195 Temporal function in a simple check constraint is inconsistent with the column type Find base table columns with a check constraint that refers to a temporal function (current_timestamp, localtimestamp, current_date, or now) that return type is inconsistent with the data type of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-08 21:43 MIT License View
196 Base table columns with a composite type Find base table columns with a composite type. Think through as to whether a column with a composite type could be replaced with a separate table. General system catalog base tables only 2020-11-06 14:51 MIT License View
197 Base table columns with an array type Find base table columns with an array type. Think through as to whether a column with an array type could be replaced with a separate table. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
198 Perhaps incorrect check of permitted timestamp values Find base table columns with a timestamp type and a simple check constraint that checks as to whether the timestamp values are within an appropriate range of values. Find constraints where the check of the upper bound of the range is perhaps incorrect. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 10:27 MIT License View
199 Perhaps a too simplified state machine Find base table columns with Boolean type that name refers to the possibility that these are used to register as to whether an entity is currently in active state or not. Find the base tables that have exactly one Boolean column. During the system design one should find all the possible states of an entity type that influence the behavior of the information system. Data as to whether an entity is in one of these states should be in the database. Having only two states - active/inactive - is sometimes a too big simplification. Problem detection INFORMATION_SCHEMA only 2021-03-27 03:08 MIT License View
200 Base table columns with CHAR(n) or VARCHAR(n) type Find base table columns with CHAR(n) or VARCHAR(n) type and make sure that n is not too big or too small. Also make sure that you do not use CHAR(n) in case of columns that have to keep variable length strings. "Values of type character are physically padded with spaces to the specified width n, and are stored and displayed that way" (https://www.postgresql.org/docs/current/datatype-character.html) General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View