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
221 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 2025-11-07 10:11 MIT License View
222 Perhaps incorrect use of 'NULL' Find Boolean expressions, queries, routines, and default values that refer to value 'NULL'. Perhaps NULL was intended instead. 'NULL' is a string (a value) but NULL is a special marker for denoting missing value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
223 Too generic names (candidate key columns) Find candidate key columns with the names like id, identifikaator, code, kood, number, etc. The names should have a prefix or a suffix. These are too generic names. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
224 Do not clone tables Find cases where a base table has been split horizontally into multiple smaller base tables based on the distinct values in one of the columns of the original table. Each such newly created table has the name, a part of which is a data value from the original tables. Find base tables that have the same columns (column name, column order, data type) and the difference between the tables are the numbers in the table names (table1, table2, etc.). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
225 Perhaps CHECK should be associated with a domain Find cases where multiple columns with the same domain have exactly the same CHECK constraint that is directly associated with the table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
226 Optional columns before mandatory columns Find cases where optional columns are before a mandatory column. Place mandatory columns before optional columns to improve comprehensibility of the table. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
227 Some CHECKS are associated with a domain and some with the base table columns that have the domain Find cases where some CHECKS are associated with a domain and some with the base table columns that have the domain. Avoid duplication of code. Write as little code as possible. If possible, move things "before the brackets" so to say. In this case it means declaring CHECKS at the level of the domain and not at the level of base table columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
228 Do not specify a list of values in a table column definition Find cases where the list of valid data values in the column is specified in the column definition (in addition to specifying the type of the column) by using, for instance, check constraints or enumerated types. The check constraint is either associated directly with a table or is associated with a domain. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
229 Do not use dual-purpose foreign keys Find cases where the same column of a base table T is used to record references to multiple base tables. In addition, one has to add additional column to T for holding metadata about the parent table, referenced by the current row. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
230 Duplicate triggers Find cases where the same table has multiple triggers with the same type (row-level, statement-level) that react to the same event with the same WHEN condition and with the same way (by invoking the same function). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
231 Short cycle (columns) Find cases where two candidate keys of the same table that are also foreign keys reference to each other. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
232 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 2025-11-07 10:11 MIT License View
233 Do not assume you must use files (based on user data) 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+system catalog base tables 2025-11-07 10:11 MIT License View
234 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
235 Table columns with NOT VALID CHECK constraints Find CHECK constraints of base table and foreign table columns that are not valid. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
236 CHECK constraints that perhaps do not consider 'infinity' and '-infinity' special values Find check constraints of base tables that cover exactly one column where the default value of the column is special value 'infinity' or '-infinity'. Find only such constraints that probably check a range of permitted values but do not consider that one of the values might be 'infinity' or '-infinity'. Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
237 The expression of a check constraint that is associated with a domain needs type conversion Find check constraints of domains where the Boolean expression invokes an operation that does not match with the data type of the domain. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
238 Perhaps checking of file extension is incorrect Find check constraints of tables that use a regular expression to check as to whether a registered string ends with an appropriate file extension. However, the expression does not put the dot sign into the square brackets nor does have the escape character \before it, i.e., it is interpreted as a single character not as the dot sign in the expression. In regular expressions the dot (.) matches any single character except the newline character. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
239 Cannot register all legal e-mail addresses Find CHECK constraints on base table or foreign table columns that contain data about e-mail addresses and apply unnecessary restrictions to the these, rejecting potentially some legal addresses. More precisely, find CHECK constraints that prevent registration of e-mail addresses with multiple @ signs. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
240 Cannot register all legal personal names Find CHECK constraints on base table or foreign table columns that contain data about personal names and apply unnecessary restrictions to the names, rejecting potentially some legal names. Find checks that prohibit a digit or require a letter A-Z. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View