PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1031 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
501 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
502 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
503 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
504 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
505 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
506 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
507 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
508 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
509 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
510 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
511 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
512 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
513 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
514 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
515 Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with exactly one column Find patterns of the names of constraints and indexes. Make sure that the naming is consistent. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
516 Patterns of the names of PRIMARY KEY, UNIQUE, CHECK, EXCLUDE, and FOREIGN KEY constraints as well as user-defined non-unique indexes that are associated with two or three columns Find patterns of the names of constraints and indexes. Make sure that the naming is consistent. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
517 PL/pgSQL functions with consecutive RETURN clauses Find PL/pgSQL functions with consecutive RETURN clauses. Only the first RETURN will be used, others are unnecessary. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
518 Perhaps unnecessary DECLARE section in a PL/pgSQL routine (2) Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the keyword DECLARE is followed by BEGIN, i.e., the DECLARE section is empty. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
519 Perhaps unnecessary DECLARE section in a PL/pgSQL routine Find PL/pgSQL routines that perhaps unnecessarily contain DECLARE section. More specifically, find routines with the DECLARE section where the only task seems to be raising an exception. The query excludes the cases where the error message is constructed dynamically, i.e., in this case using a variable maybe justifiable. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
520 Incorrect comparison operator Find PL/pgSQL routines that use comparison operators =< or =>. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View