PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 24 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
1 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
2 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
3 BOOLEAN base table and foreign table columns with a CHECK constraint that involves olnly this column Find base table and foreign table columns with the Boolean type that has a CHECK constraint that involves only this column. Avoid unnecessary CHECK constraints. The Boolean type contains only two values and there is nothing to check. By creating a check that determines that possible values in the column are TRUE and FALSE, one duplicates the attribute constraint (column has a type). This is a form of duplication. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
4 BOOLEAN base table and foreign table columns with a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves olnly this column Find base table columns with the Boolean type that has a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint that involves only this column. Avoid unnecessary constraints. It is quite improbable that there must be such constraints. For instance, a table with PRIMARY KEY () or UNIQUE () constraint can have at most two rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-03 10:46 MIT License View
5 CHECK constraints on columns with Boolean data Find check constraints that involve columns with the type Boolean. General INFORMATION_SCHEMA only 2020-12-27 15:09 MIT License View
6 Columns of base tables that hold truth values but do not have a default value (Boolean columns) Find columns of base tables that have type BOOLEAN but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. Often it should be possible to select one of these as the default value of a column that has BOOLEAN type. Problem detection INFORMATION_SCHEMA only 2023-11-09 13:14 MIT License View
7 Columns of base tables that hold truth values but do not have a default value (non-Boolean columns) Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It could be possible to select one of these as the default value in case of the columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-20 14:08 MIT License View
8 Columns of base tables that hold truth values but do not restrict the permitted values (non-Boolean columns) Find non-foreign key columns of base tables that probably (based on the column name) contain values that represent truth values but do not have a have a check constraint. The constraint should restrict the permitted values with values that represent truth values TRUE and FALSE. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-20 14:07 MIT License View
9 Columns of base tables that hold truth values that do not have a default value although they could have it (Boolean columns) Find columns of base tables that have type BOOLEAN. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column. Problem detection INFORMATION_SCHEMA only 2023-11-09 16:39 MIT License View
10 Columns of base tables that hold truth values that do not have a default value although they could have it (non-Boolean columns) Find columns of base tables that do not have type BOOLEAN but are used to record Boolean values. Based on column names these implement a state machine or record agreements. At the same time the columns do not have a default value. There are only two truth values - TRUE and FALSE - in case of two-valued logic. It should be possible to select one of these as the default value of the column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 16:40 MIT License View
11 Columns with BOOLEAN type that do not have a good name The phrase "is_" or "has_" or "can_" (in English) or "on_" (in Estonian) should be used in the name - preferably in the beginning. Worse: agreed, kinnitatud. Better: contract_is_agreed, leping_on_kinnitatud. Perhaps the best: is_contract_agreed or is_agreement, on_leping_kinnitatud, on_kinnitatud. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-03 09:56 MIT License View
12 Consistency of using NOT NULL constraints on Boolean base table columns Find the number of mandatory and optional Boolean base table columns and the proportion of optional columns from all the Boolean columns. 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. Sofware measure INFORMATION_SCHEMA only 2020-12-28 01:43 MIT License View
13 Inconsistent naming of columns with BOOLEAN type Find as to whether some columns with the type Boolean start with a prefix ("is", "has", "can", "on") and some do not. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-03 09:54 MIT License View
14 Names of columns with the type BOOLEAN The naming of BOOLEAN columns must be consistent. For the better readability the names of such columns could have prefix "is_" (in English) or "on_" (in Estonian) General INFORMATION_SCHEMA+system catalog base tables 2024-01-03 09:41 MIT License View
15 Perhaps a CHECK constraint about the combination of truth values is missing Find base tables that have at least two columns that have Boolean type and have at least one Boolean column that is not covered by a CHECK constraint involving more than one Boolean column. The Boolean columns possibly mean that we want to record data about states. Often the states depend on each other. For instance, if an order is archived it must be inactive. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
16 Perhaps a state machine is implemented with Boolean columns Find implementations of state machines that uses a set of one or more Boolean columns. These columns could have the type Boolean or could probably (based on the column name and non-participation in a foreign key) contain values that represent truth values. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 13:22 MIT License View
17 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
18 Perhaps the type of a base table column/domain should be BOOLEAN (based on CHECK constraints) Find base table columns and domains that have a CHECK constraint that limits possible values in a manner that seems to indicate that the permitted values represent truth values, i.e., permitted values are 0/1 or true/false. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
19 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 2021-02-25 17:30 MIT License View
20 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