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
21 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
22 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
23 Base table columns for recording geographic coordinates (based on column names) Find base table columns that are according to the name meant for recording coordinates. General INFORMATION_SCHEMA only 2021-10-08 11:59 MIT License View
24 Base table columns for recording geographic coordinates that do not have a suitable type (based on column names) Find base table columns that are according to the name meant for recording geographic coordinate but do not have a suitable type (numeric or point). Problem detection INFORMATION_SCHEMA only 2021-10-08 12:01 MIT License View
25 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
26 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
27 Base table columns with DECIMAL (p, s) or NUMERIC (p, s) type Find base table columns with DECIMAL (p, s) or NUMERIC (p, s) type and make sure that precision p and scale s are not too big or too small. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
28 Base table columns with one of the following types: BIGINT, INTEGER, TEXT, or VARCHAR without max character length Each column should have the most appropriate data type. Developers sometimes misuse BIGINT, INTEGER, TEXT or VARCHAR type in places where a type that permits smaller values would be more appropriate and semantically descriptive. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
29 Base table columns with SMALLINT or BOOLEAN type Each column should have the most appropriate data type. Developers sometimes forget to use SMALLINT type even if it is logically the best choice. Developers also sometimes forget to use BOOLEAN type and instead invent something. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
30 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
31 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
32 Base table columns with the type VARCHAR(1) The choice of data types should reveal as much as possible about the nature of the data in the column. The type of these columns could be CHAR(1) and they should have a constraint that a value in the column cannot be an empty string. Problem detection INFORMATION_SCHEMA only 2024-12-14 13:41 MIT License View
33 Base table columns with UUID type Each column should have the most appropriate data type. If one does not want to have in a table the surrogate key that values are generated by using a sequence generator, then one may use instead a column with Universally Unique Identifiers as the key column. General INFORMATION_SCHEMA only 2021-03-07 20:57 MIT License View
34 Base tables and foreign tables that have no CHECK constraints What are the base tables and foreign tables without any associated (directly or through domains) check constraints? A NOT NULL constraint is a kind of CHECK constraint. However, this query does not take into account NOT NULL constraints. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:29 MIT License View
35 Base tables that have a unique constraint but not the primary key A common style is to declare in each base table one of the candidate keys as the primary key. All the other candidate keys would be alternate keys that will be enforce with the help of UNIQUE + NOT NULL constraints. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
36 Base tables that have neither a unique constraint nor the primary key Find base tables without any unique constraints and the primary key. In such tables there are no restrictions for recording duplicate rows. Each row represents a true proposition about the real world. It does not make the proposition truer if one presents it more than once. Moreover, duplicate rows increase data size. Without keys the DBMS lacks vital information about data in the database that it can internally use to choose better execution plans and in this way improve performance of database operations. The only legitimate reason of such a table is if it is an abstract table that is used to define common columns of subtables. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
37 Base tables that have only the surrogate key and do not have any other column Do not create unnecessary tables. If a table has cardinality 1 (one column), then most probably the values in this column should not be system generated unique values. Problem detection INFORMATION_SCHEMA only 2021-03-08 00:41 MIT License View
38 Base tables where all the columns are optional Find base tables where all the columns are optional, i.e., permit NULLs. In such tables can be rows with no identity value and thus indistinguishable from other rows. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
39 Base tables where certainly registration time is not recorded Find base tables that do not have any column with a timestamp type. In such tables certainly registration time is not recorded. Make sure as to whether recording registration time is necessary. General INFORMATION_SCHEMA only 2021-02-26 00:41 MIT License View
40 Base tables with multiple Boolean columns Find base tables that have more than one column with Boolean type. General INFORMATION_SCHEMA only 2021-02-19 17:41 MIT License View