PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1050 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
621 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
622 PL/pgSQL functions without the RETURN clause The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. This restriction does not apply to functions with output parameters and functions returning void, however. In those cases a RETURN statement is automatically executed if the top-level block finishes. It also does not apply to trigger functions that only task is to raise an exception. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
623 PL/pgSQL routines that use a cursor Working with sets of rows rather than processing each row separately is more effective. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
624 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 2025-11-07 10:11 MIT License View
625 Pointlessly returned value The value that is returned by a function should depend on something. If you do not have anything meaningful to return (for instance, the function always returns true, false, NULL, or the same number), then write VOID instead of the return type. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
626 Polymorphic routines Find routines that have a parameter (input or output) that can have values from different types. This parameter has one of the PostgreSQL polymorphic types. The set of polymorphic types in PostgreSQL is a proper subset of its pseudo-types. The use of such a parameter allows a single routine definition to operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular call. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
627 Positional argument usage in multi-parameter routine calls This query audits user-defined routine invocations to enforce named parameter notation, specifically targeting calls to routines with multiple input parameters. It identifies invocations using positional notation (e.g., func(a, b)), which couples the calling logic to the specific order of the callee's signature. Crucially, the query excludes invocations of routines that accept a single input parameter. In single-arity contexts, positional notation is structurally unambiguous and widely accepted. The focus is strictly on multi-parameter calls where positional notation increases the risk of argument misalignment during refactoring. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-26 12:10 MIT License View
628 Potentially a classifier is missing (based on field sizes) Find columns that are not covered by a primary key, unique, and foreign key constraint but have a textual type with the maximum field size 3 or less. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
629 Potentially missing PRIMARY KEY or UNIQUE constraints (based on column names) Find columns of base tables that name refers to the possibility that it contains unique values but the column does not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
630 Potentially missing PRIMARY KEY or UNIQUE constraints (based on foreign keys) All the keys must be enforced. Find base tables that implement M:N relationship types and that allow multiple relationships of the same type between the same entities. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
631 Potentially missing PRIMARY KEY or UNIQUE constraints (based on sequence generators) Find columns of base tables that contain automatically generated unique values but do not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
632 Potentially missing PRIMARY KEY or UNIQUE constraints (based on UUIDs) If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Find columns that contain Universally Unique Identifiers but are not a part of any simple PRIMARY KEY/UNIQUE constraint and are also not part of a foreign key. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
633 Potentially missing sequence generators (based on column names and types) Find surrogate key columns that do not have an associated sequence generator. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
634 Potentially unnecessary use of sequence generators Find simple natural key columns that are associated with a sequence generator. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
635 Precise comparison with pattern matching in CHECK constraints Find CHECK constraints that use precise comparison (= or <>) with a regular expression or LIKE pattern. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
636 Predefined character classes must be between double square brackets Find regular expressions that do not have predefined character classes between double square brackets, e.g., [:digit:] instead of [[:digit:]]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
637 Prefer Polymorphism to If/Else or Switch/Case Find routines with IF/ELSE or SWITCH/CASE statements. If your routine has a multipart IF/CASE statement, then perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
638 Prefer Polymorphism to If/Else or Switch/Case (2) Find routines with multiple raise exception commands. Perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
639 Prefixes in the names of database objects Find for different types of database objects all the prefixes that are used in different names. One should be consistent in naming, including in the use of prefixes. If you use prefix in the name of a database object, then it could refer to the type of the database object. Do not use different prefixes in the names of database objects that have the same type. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
640 Primary key columns are not the first in a table In SQL tables each column has the ordinal position. Find all the base tables where the primary key columns are not the first in the table, i.e., there is at least one non-primary key column that comes before a primary key column. It is easier to grasp the primary key if its columns are the first in the table. It could be that a table inherits from an abstract table where no keys have been defined. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View