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
281 CHECK constraint cardinality is zero Write correct constraints. Usually the constraint expression should refer to at least one column. A domain constraint expression should refer to the stub VALUE. For instance, the constraint CHECK(1=0) that is associated with a table T would prevent adding any rows to T. The value of the Boolean expression of this constraint is always FALSE. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-04-22 17:06 MIT License View
282 CHECK constraints are inconsistent with DEFAULT values Find table CHECK constraints that involve two columns that have the same default value. However the constraint assumes that the values must be unequal or one value must be bigger than another. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-11-30 09:58 MIT License View
283 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
284 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
285 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
286 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
287 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
288 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
289 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
290 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
291 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
292 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
293 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
294 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
295 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
296 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
297 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
298 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
299 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
300 Columns of derived tables that name has been given by the system Find columns of derived tables (i.e., views and materialized views) where in the creation statement of the table the name of the column has not been specified, i.e., it is generated by the system. Problem detection system catalog base tables only 2023-12-21 12:15 MIT License View