PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1018 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
681 Perhaps too many subconditions in a CHECK constraint Find check constraints of base table and foreign table columns that are either associated with more than one column and have at least one AND operation or are associated with exactly one column and have two or more AND operations. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
682 Perhaps Trim is missing Find derived tables (views and materialized views) that apparently concatenate values (by using || operator or use Concat function or use Format function) by putting a space between these but do not use Trim function to get rid of the extra space at the beginning or the end of the string. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
683 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
684 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
685 Perhaps unnecessary privileges to use the database Find as to whether a database user (except postgres), who is not a superuser, has Create (C) or Temporary (T) privileges to use the database. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
686 Perhaps unnecessary Trim Find constraints where the use of Trim function is possibly unnecessary. CHECK constraint column!~'^[[:space:]]*$' already ensures that the values in the column cannot be empty strings or strings that consist of only whitespace. Trim(Column)!~'^[[:space:]]*$' - in this case the use of Trim function is unnecessary. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
687 Perhaps unnecessay regular expression Find occurrences of possibly pointless regular expressions - i.e., value contains zero or more symbols. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
688 Perhaps updating of modification time is missing Find routines with SQL-standard body that seem to update data in a table that has a column for modification time but the routine does not seem to update the modification time while updating the row and the table does not seem to have an UPDATE trigger that changes the modification time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
689 Perhaps USING syntax could be used for joining in the subqueries of derived tables Find derived tables that use newer join syntax where join conditions are written in the WHERE clause but do not use USING synatx. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
690 Permitting in a column only empty strings and strings that consist of whitespace characters This query identifies logically flawed CHECK constraints on table columns. It specifically searches for constraints that are defined in such a way that they only permit the insertion of an empty string or strings composed entirely of whitespace characters. This behavior is invariably a bug, likely a typo in the constraint's logic (e.g., using ~ instead of !~), which effectively renders the column unusable for storing meaningful data. Problem detection INFORMATION_SCHEMA only 2025-11-09 10:47 MIT License View
691 Personal names are unique Find all primary key and unique constraints of base tables that involve at least one column with personal names. Personal names are not unique and these cannot be (usually) used to guarantee uniqueness. Either one has declared a superkey instead of candidate key (i.e., the key contains a column that is not needed for uniqueness) or the key columns have been found incorrectly. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
692 Phone number column has an incorrect data type Find the columns where the name reffers to the possibility that the column contains phone numbers but the type of the column is not VARCHAR or TEXT. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
693 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
694 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
695 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
696 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
697 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
698 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
699 Potential duplication of sequence generators Do not create unnecessary sequence generators. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
700 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