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
561 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 2023-12-06 12:03 MIT License View
562 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 2021-02-25 17:29 MIT License View
563 All parameters with DEFAULT values Find parameters of user-defined routines that have a default value. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
564 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 2023-01-14 20:21 MIT License View
565 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 2023-01-06 18:03 MIT License View
566 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 2023-01-06 18:03 MIT License View
567 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 2020-11-06 14:51 MIT License View
568 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 2020-11-06 15:13 MIT License View
569 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 2024-12-21 11:15 MIT License View
570 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 2022-11-13 16:06 MIT License View
571 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 2023-01-14 20:52 MIT License View
572 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 2023-01-14 20:52 MIT License View
573 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 2021-12-16 11:38 MIT License View
574 Routines that use old syntax for limiting rows Find PL/pgSQL routines and SQL routines that do not have SQL-standard body that use unstandardized LIMIT clause instead of standardized FETCH FIRST n ROWS clause. The query excludes routines that are a part of an extension. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-10-29 11:34 MIT License View
575 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 2023-12-23 13:39 MIT License View
576 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 2023-12-30 10:19 MIT License View
577 Incorrect comparison operator Find PL/pgSQL routines that use comparison operators =< or =>. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-11 14:50 MIT License View
578 PL/pgSQL routine with plain SELECT Find PL/pgSQL that contain a SELECT statement that is not a SELECT … INTO statement. This is not permitted in PL/pgSQL routines. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-10-25 17:07 MIT License View
579 Sequence generators not needed Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-18 10:00 MIT License View
580 Simple natural primary keys Find primary keys that consist of one column and that values are not generated by the system. General INFORMATION_SCHEMA+system catalog base tables 2021-03-08 00:47 MIT License View