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
141 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
142 CHECK constraints on columns with personal names Find CHECK constraints on base table or foreign table columns that contain data about personal names. Make sure that the constraints do not restrict registration of legal names. General INFORMATION_SCHEMA only 2023-12-25 12:51 MIT License View
143 CHECK constraints on columns with temporal data If your table contains columns with temporal data, then it will be appropriate to restrict the range of possible values in these columns because some of the values that belong to the type might not be appropriate (for instance, imagine a client who was born in 1100-12-03 or a contract that was registered in 3890-12-12- 12:45). If your table contains multiple columns with temporal data that denote events, then the rule about the order of the events must be enforced, if possible. General INFORMATION_SCHEMA only 2023-12-25 12:37 MIT License View
144 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 2024-05-08 18:58 MIT License View
145 CHECK constraints that perhaps incorrectly consider 'infinity' and '-infinity' special values Such special values belong to the types DATE, TIMESTAMP, NUMERIC, REAL, and DOUBLE PRECISION. No value can be bigger than infinity or smaller than -infinity. If the check constraint cheks that a value must be bigger than -infinity or smaller than infinity, then it does not restrict (almost) anything. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-12-07 20:13 MIT License View
146 CHECK constraints that use non-deterministic functions Discover incorrect usage of non-deterministic functions in CHECK constraints. Find base table columns and foreign table columns that have a CHECK constraint that refers to a non-deterministic function that returns current date/time/timestamp. General INFORMATION_SCHEMA only 2020-11-06 14:51 MIT License View
147 CHECK constraints with IS NULL Find CHECK constraints to one column (associated with a base table directly or through domain) that check that the value is missing (IS NULL). Write as simple constraint definitions as possible. By default columns are optional, i.e., they permit NULLs. NULL in a column means that checking of a CHECK constraint on the column results with UNKNOWN. CHECK constraints permit rows in case of which checking results with TRUE or UNKNOWN. In case of a CHECK constraint there is no need to check separately that a value in the column could be missing, i.e., be NULL. Thus, for instance, instead of writing CHECK (price>0 OR price IS NULL) write CHECK (price>0). Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
148 CHECK constraints with the cardinality bigger than one Find multicolumn CHECK constraints. Such constraints must be associated directly with a base table, i.e., these cannot be associated with a domain. Enforce as much data integrity as possible at the database level and prefer declarative constraints to a trigger. General system catalog base tables only 2023-12-25 12:38 MIT License View
149 CHECK constraints with the cardinality bigger than one that involve the same set of columns CHECK constraints with the cardinality bigger than one that involve the same set of columns. Make sure that there is no duplication. General system catalog base tables only 2023-12-25 12:39 MIT License View
150 CHECK constraints with unnecessary CHECK of NULLs Find CHECK constraints that unnecessarily check that a value is missing. In a CHECK constraint, it is not necessary to check whether a value is NULL, because a CHECK constraint rejects rows where the condition evaluates to FALSE and allows rows where the condition evaluates to TRUE or UNKNOWN. If a value is missing (i.e., NULL), the result of the check is UNKNOWN. Therefore, for example, CHECK (price IS NULL OR price > 0) is equivalent to CHECK (price > 0). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-05-03 13:24 MIT License View
151 CHECK constraint with pattern matching on non-textual columns Find base table and foreign table columns that do not have a textual type but have a single-column check constraint that uses pattern matching. The use of a regular expression, a LIKE clause, or a SIMILAR TO clause in order to constrain values in a non-textual column points to the incorrect selection of operator or column data type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
152 CHECKs are associated with a column instead of the domain of the column Find simple check constraints (involve one column) that are associated with a base table column instead of the domain of the column. Common checks of data in columns that share the same domain should be described at the level of domain not at the level of columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
153 Coalesce/Concat need at least two arguments Find user-defined routines and derived tables (views/materialized views) that have a subquery that contain an invocation of Coalesce or Concat function with just one argument. You should use the Coalesce and Concat functions correctly by invoking these with at least two arguments. If one invokes these functions with one argument, then the functions will return the input value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-12-08 11:49 MIT License View
154 Column name contains the table name Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. General INFORMATION_SCHEMA+system catalog base tables 2023-01-06 12:38 MIT License View
155 Column name is the same as the table name Find columns that have the same name as the table. The names may have different uppercase/lowercase characters. Sometimes columns with such names are used as the key columns. Make sure that the naming style is consistent. General INFORMATION_SCHEMA+system catalog base tables 2023-01-06 12:38 MIT License View
156 Column names that make joining more difficult (foreign key column name contains the table name) Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different but the difference comes from the fact that the foreign key column name starts or ends with the table name. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-12 10:26 MIT License View
157 Column names that make joining tables more difficult Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-08 13:56 MIT License View
158 Column names that make joining tables more difficult (quite similar names) Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is between two and four characters. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-08 13:56 MIT License View
159 Column names that make joining tables more difficult (very similar names) Find foreign keys where the name of the foreign key column and the corresponding primary key/unique column is different and the difference is one character. Exclude foreign key columns that refer to the key of the same table because within each named table column names must be different. Simplify writing queries that have to join data from multiple base tables. If the columns that participate in the join condition have the same name, then one can use the USING syntax. Problem detection system catalog base tables only 2023-11-08 13:56 MIT License View
160 Columns defined in a subtable Find columns that have been added to a subtable, i.e., these were not defined in its immediate supertable. General INFORMATION_SCHEMA+system catalog base tables 2021-01-02 03:22 MIT License View