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
881 Triggers with the same name within the same schema Find names of triggers that are used within the same schema more than once. Give different triggers different names. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
882 Trying to lock a value instead of a row This query identifies SQL routines that utilize explicit row locking clauses (e.g., FOR UPDATE, FOR SHARE) in queries that do not target a specific base table or relation. For instance, a statement like SELECT 'text' AS v FOR UPDATE attempts to apply a lock to a scalar constant. Since row-level locks in PostgreSQL require a physical row version (tuple) within a table to be effective, such statements are semantically void. They indicate a fundamental misunderstanding of the concurrency control mechanism and should be corrected to target actual table rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-10 13:00 MIT License View
883 Tsvector values are not automatically calculated Find base table columns with tsvector type in case of which it is not a generated column nor does the table has an associated trigger to calculate the tsvector value automatically. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
884 Unbalanced brackets Write expressions correctly. Find code fragments that have unbalanced brackets, i.e., the number of opening brackets is not the same as the number of closing brackets. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
885 Unbounded textual columns for non-descriptive attributes This query identifies base table columns defined as unbounded TEXT or VARCHAR (without a length specifier) that lack any corresponding CHECK constraint to restrict value length. It explicitly excludes foreign key columns and columns heuristically identified as descriptive fields (e.g., names containing "comment", "description", "note"), where arbitrary length is typically acceptable. For structured attributes (such as names, codes, or identifiers), relying on unbounded types without constraints is a design risk, potentially allowing excessive data payload, complicating index usage, and violating domain constraints. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-27 10:19 MIT License View
886 Unique constraints made redundant by an exclude constraint This query identifies superfluous UNIQUE constraints where the constraint is logically subsumed by a more general EXCLUDE constraint on the same table. It targets cases where the set of columns in a UNIQUE or PRIMARY KEY constraint is a subset of (or equal to) the columns in an EXCLUDE constraint, provided the EXCLUDE constraint uses the equality operator (=) for those same columns. In this scenario, the EXCLUDE constraint already enforces uniqueness as part of its more complex logic, rendering the separate UNIQUE constraint redundant. Eliminating this duplication improves schema clarity and removes an unnecessary constraint check. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 09:56 MIT License View
887 Unique index definition instead of a key declaration Find cases where a unique constraint (that is not case-insensitive or partial, i.e., applies only to certain rows) has been enforced by using a CREATE UNIQUE INDEX statement instead declaring a PRIMARY KEY, UNIQUE, or EXCLUDE constraint. You should try to work on as high level of abstraction as possible. According to the ANSI-SPARC Architecture indexes are a part of internal database schema whereas constraints are a part of conceptual schema, i.e., at the higher level of abstraction. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
888 Unnamed columns in routines Find user-defined routines that contain a SQL statement that does not give the name to a column in a SQL statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
889 Unnamed parameters Find unnamed parameters in PL/PGSQL routines that do not declare aliases for parameters and in SQL routines. Avoid unnamed parameters because dependency on position in case of referencing the parameters makes evolving the code more difficult. In case of unnamed parameters - if one changes the order of parameters in the routine signature, then one has to change the body of the routine in order to use correct references. The bigger is the number of parameters in a routine the more the unnamed parameters make it more difficult to understand the routine. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
890 Unnecessary domains Domain is a reusable artifact. Effort of its creation should be paid off by the advantages that it offers. If a domain is used in case of at most one column of a base table or even if it is used in case of more than one column but it does not specify neither a default value nor a check constraint, then there is no point of creating the domain. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
891 Unnecessary explicit locking in read-only routines This query identifies read-only routines that unnecessarily utilize explicit locking mechanisms. PostgreSQL's Multi-Version Concurrency Control (MVCC) ensures that readers do not block writers (and vice versa) for standard query operations. Therefore, routines that perform no data modification (DML) and do not raise exceptions have no functional need to acquire table-level (LOCK TABLE) or row-level (FOR SHARE/UPDATE) locks. Using them in this context provides no benefit while actively degrading system concurrency by blocking other transactions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-10 13:23 MIT License View
892 Unnecessary privileges to use trigger functions A user that corresponds to an application does not have to have privileges to use trigger functions. If it has these, then it violates the principle of least privilege. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
893 Unnecessary usage of the numeric type in case of base table columns Find base table columns that have type NUMERIC and the scale is 0, i.e., one can record in the column only integer values. Arithmetic operations are slower in case of the numeric type compared to an integer type. Thus, in order to record integer values, one should use columns with the type SMALLINT, INTEGER, or BIGINT instead of NUMERIC(p,0). Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
894 Unnecessary usage privileges of PUBLIC PostgreSQL gives by default some privileges to all the present and future database users (PUBLIC). Find usage privileges of collations, domains, foreign data wrappers, foreign servers, and sequences that have been given to PUBLIC. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
895 Unnecessary use of Coalesce This query identifies redundant null-handling logic within string manipulation expressions. It targets the use of explicit coalesce() calls nested inside functions that are already null-safe, such as concat(), concat_ws(), or format(). Since these functions implicitly treat NULL arguments as empty strings (or ignore them), wrapping arguments in coalesce(arg, '') is superfluous. The query deliberately excludes expressions using the standard concatenation operator (||), as coalesce() is legitimately required in that context to prevent null propagation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-03 15:58 MIT License View
896 Unnecessary use of gist index type in case of an exclude constraint Find exclude constraints that are based on the gist index type although the default b-tree index type would have been enough. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
897 Unsecure SECURITY DEFINER routines SECURITY DEFINER routines must be secured against the malicious use of pg_temp schema. Find routines that do not explicitly set the search path or do it incorrectly (the search path is between quotation marks) and are thus potential targets of the attack. pg_temp must be the last entry in search_path. The lack of search_path is allowed only if the SQL statements in routines explicitly refer to the schemas that contain the schema objects. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
898 Unused composite types (for table columns, typed tables, input and output parameters) Find user-defined composite types that are not used in case of any table, column, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
899 Unused domains (for base table columns and parameters) Find domains that are not used in case of any base table column and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2025-11-07 10:11 MIT License View
900 Unused enumerated types (for base table columns, domains, and parameters) Find enumerated types that are not used in case of any base table column, domain, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View