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
381 Inconsistent use of plural and singular in table names in the context of a relationship (Estonian version) Find foreign key constraints in case of which the name of one of the tables is in plural and the name of another table is in singular. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
382 Inconsistent use of plural and singular in column names in the context of a relationship (English version) Find foreign key constraints that cover one column in case of which the name of refererenced/referencing column is in plural and the name of referencing/refererenced column is in singular. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
383 Foreign key references a non-key (has optional columns) Find foreign key constraints that referenced column is optional. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
384 Cascading update is not needed (based on surrogate keys) Find foreign key constraints that reference to a candidate key that is a surrogate key, i.e., its values are generated by the system by using sequence generators. Do not use ON UPDATE CASCADE, ON UPDATE SET NULL, and ON UPDATE SET DEFAULT in case of foreign keys that reference to surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
385 Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
386 Foreign key constraint references to the columns of a UNIQUE constraint not to the columns of the PRIMARY KEY constraint while the referenced table has the primary key Find foreign key constraints that reference to a UNIQUE constraint columns not to the PRIMARY KEY constraint columns while at the same time the referenced table does have the primary key. This is legal in SQL. However, a tradition is to refer to the primary key columns. If most of the foreign keys refer to the primary key columns, then it raises a question as to whether this kind of design decision has a good reason in a particular case or whether it is an inconsistency. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
387 Foreign key references to a unique index columns not a unique key columns Find foreign key constraints that reference to the columns that are covered by a unique index not a unique key. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
388 Foreign key refers to a table that has at least one subtable in the inheritance hierarchy Find foreign key constraints that refer to a base table that has at least one subtable in the inheritance hierarchy. Rows of the subtable do not belong to the supertable in terms of checking the referential integrity. Let us assume that there is a table T with a subtable Tsub. Let us also assume that table B has a foreign key that refers to the table T. If a row is inserted into Tsub, then this row cannot be referenced from B. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
389 SET NULL compensatory action is unsuitable Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
390 Inconsistency of using column data types/field sizes in case of columns that implement relationships Find foreign key constraints where the candidate key columns (belong to a PRIMARY KEY/UNIQUE constraint) and foreign key columns do not have the same data type and field size. Primary key/unique columns and foreign key columns should have the same data type and field size. If, for instance, the primary key column has type INTEGER and foreign key column has type SMALLINT, then one cannot use all the primary key values as foreign key values. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
391 Composite foreign keys Find foreign keys that consist of more than one column. Make sure that the order of columns in the composite foreign key corresponds to the order of columns in the composite candidate key in the referenced table. General system catalog base tables only 2025-11-07 10:11 MIT License View
392 SET DEFAULT compensatory action is unsuitable Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
393 Unused foreign servers Find foreign servers that do not have any associated foreign tables. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
394 Foreign servers without user mappings Find foreign servers that do not have any associated user mappings. "A user mapping typically encapsulates connection information that a foreign-data wrapper uses together with the information encapsulated by a foreign server to access an external data resource." Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
395 Function Upper or Lower is used in an index on a non-textual column Find function-based indexes that are based on function Upper or Lower but have been defined on a non-textual column. Such indexes support case insensitive search but in case of non-textual columns this does not have a meaning. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
396 Generated stored base table columns that expression does not refer to any column Find generated stored base table columns that expression does not refer to any column of the table. It could be that there will be a constant value in every row in case of this column. The support of generated columns was added to PostgreSQL 12. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
397 Generated stored base table columns duplicates another column in the table Find generated stored columns in PostgreSQL base tables that duplicate other columns in the table. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
398 Generated stored base table columns Find generated stored columns in PostgreSQL base tables. The support of generated columns was added to PostgreSQL 12. These could be used to improve the performance of queries. General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
399 Referential degree of tables (ver 2) Find how many base tables are referenced from a base table by using foreign keys. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View
400 The number of referencing base tables Find how many base tables reference to a base table by using foreign keys. The more references there are the more impact the change of the table could have to the overall system. Sofware measure system catalog base tables only 2025-11-07 10:11 MIT License View