PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1036 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
481 One-to-one relationships Find one-to-one relationships between base tables. In this case the foreign key columns must have primary key or unique constraint. These tables could implement inheritance hierarchy that has been specified in the conceptual data model. General system catalog base tables only 2025-11-07 10:11 MIT License View
482 Optional base table columns Find optional base table columns, i.e., columns that permit NULLs. Are you sure you want to allow NULLs in these columns? General INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
483 Perhaps default value 'infinity' is missing Find optional base table columns that have a timestamp type and do not have a default value. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
484 Optional non-foreign key base table columns that participate in a UNIQUE constraint or index Find optional base table columns that participate in a UNIQUE constraint or index but do not participate in a foreign key constraint. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
485 Optional base table columns that participate in a UNIQUE constraint or index Find optional base table columns that participate in a UNIQUE constraint or index. Each base table has one or more candidate keys. One of these is usually selected to be the primary key, other are called alternate keys. To enforce an alternate key one should define a UNIQUE constraint and determine that all the key columns are mandatory (NOT NULL) just like the primary key columns are mandatory. Make sure that the NOT NULL constraint is not missing on these columns. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
486 Optional composite foreign keys that do not have MATCH FULL specified Find optional composite foreign keys that do not have MATCH FULL specified. Without MATCH FULL the system will permit partial foreign key values Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
487 Duplicate non-fuction based unique indexes Find pairs of non-function based unique indexes that cover the same set of columns. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. For instance, it helps us to find unique indexes that have been defined to already unique columns. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
488 Definition of a non-minimal superkey instead of a candidate key (based on unique indexes) Find pairs of non-partial unique indexes where the columns of a index are a proper subset of the columns of another index. Include indexes that support a constraint (primary key, unique, exclude), i.e., these indexes have been automatically created due to the constraint declaration. Exclude the pairs where both participants have been created to support a constraint. Candidate key is a minimal superkey, meaning that it is not possible to remove columns from the candidate key without losing its uniqueness property. One should define keys based on candidate keys, i.e., the keys should not have redundancy in terms of columns. Defining a unique index essentially means defining a key in the table but it is done at the lower level of abstraction. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
489 All short cycles (tables) Find pairs of tables that have both a foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
490 Short cycles (tables) Find pairs of tables that have both a mandatory (NOT NULL) and not defrerrable foreign key that references to the other table. Such cycles can involve more than two tables but the query detects only cycles with two tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
491 Too generic names (parameters) (there is a parameter with a more specific name in the routine) Find parameter names in case of which the same routine has another parameter with the same mode but with more specific name, i.e., the name contains the parameter name in the end or in the beginning. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
492 Inconsistency of using parameter data types Find parameters of routines that have the same name but a different type. Parameters that have the same name should have, in general, the same data type as well, assuming that the routines, which have the parameters, have different names, i.e., there is no overloading in play. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
493 All parameters with DEFAULT values Find parameters of user-defined routines that have a default value. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
494 Paramtetes with an array type, XML, JSON, or JSONB type Find parameters of user-defined routines that type is an array type, xml, json, or jsonb type. Make sure that the parameter name matches the type (perhaps should be in plural). General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
495 Parameter name contains the routine name Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
496 Parameter name is the same as the routine name Find parameters that have the same name as the routine. The names may have different uppercase/lowercase characters. Make sure that the naming style is consistent. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
497 Partial or case insensitive unique indexes Find partial or case insensitive unique indexes. These implement uniqueness constraints that are impossible to enforce with the help of SQL's regular UNIQUE constraint. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
498 All declaratively partitioned tables Find partitioned tables that have been implemented by using the declarative approach. Declarative partitioning is implemented in PostgreSQL starting from PostgreSQL 10. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
499 Too many slashes in regular expressions Find patterns of regular expressions where more than \ is written instead of \, e.g., \\s is used instead of \s to refer to a character class. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
500 Patterns of the Boolean expressions of simple CHECK constraints Find patterns of the Boolean expressions of simple CHECK constraints (involve only one column). Do not solve the same task in different places differently. The same rule could be implemented with CHECK constraints that have different Boolean expressions. "If you do something a certain way, do all similar things in the same way." (Robert C. Martin, Clean Code) General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View