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
621 Many-to-many relationship types that do not permit repeating relationships and do not have additional attributes Find base tables that implement many-to-many relationship types that do not permit repeating relationships. More specifically find tables that have two or more foreign keys and all the columns of the table belong to a foreign key. In addition, all the table columns must be covered by a primary key or unique constraint. It could be that during the system analysis a mistake has been made and some attributes of the entity type that represents the relationship type have not been discovered. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-28 12:04 MIT License View
622 Phone number column has an incorrect data type Find the columns where the name reffers to the possibility that the column contains phone numbers but the type of the column is not VARCHAR or TEXT. Problem detection INFORMATION_SCHEMA only 2021-03-27 19:00 MIT License View
623 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
624 Storing file content in the database Find columns that probably store content of files in the database. General INFORMATION_SCHEMA only 2021-03-27 17:02 MIT License View
625 Do not assume you must use files Find cases where you store images and other media as files outside the database and store in the database only paths to the files. Problem detection INFORMATION_SCHEMA only 2021-03-27 16:55 MIT License View
626 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
627 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
628 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
629 Perhaps a state machine is implemented with timestamp columns Find implementations of state machines that uses a set of columns with a timestamp type. Problem detection INFORMATION_SCHEMA only 2021-03-26 21:04 MIT License View
630 Different character maximum lengths that are used to define textual base table columns Find the number of different character maximum lengths that are used to define textual base table columns as well as list all the different lengths. Show also the total number of columns with char/varchar type. Maximum character length constrains values in a column. Thus, in case there is a small number of used lengths, it raises a question as to whether the lengths have been optimally selected. Sofware measure INFORMATION_SCHEMA only 2021-03-26 11:24 MIT License View
631 The number of tables based on the number of columns Find how many tables with a certain range of the number of columns there are in the database. Sofware measure INFORMATION_SCHEMA+system catalog base tables 2021-03-25 11:17 MIT License View
632 Inconsistent data type usage in case of registering a date Find as to whether there are multiple different types used in case of columns that are meant for registering dates. Problem detection INFORMATION_SCHEMA only 2021-03-21 18:32 MIT License View
633 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
634 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 2021-03-21 17:00 MIT License View
635 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
636 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
637 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
638 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
639 Three-valued logic (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 NOT NULL constraint. 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 columns mandatory. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-03-20 14:07 MIT License View
640 The number of base table columns by type category Find the number of base table columns based on the category of data type that the columns have. Sofware measure INFORMATION_SCHEMA only 2021-03-20 12:27 MIT License View