PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1041 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
521 Incorrect use of non-deterministic functions in CHECK constraints Do not create a constraint in case of which data that satisfied a constraint c at the registration time suddenly does not satisfy it any more as the time goes by. Find all the check constraints that use non-deterministic functions (now, current_timestamp, localtimestamp, current_date, current_time) in a way that makes this situation possible. Fort instance, localtimestamp(0)>end_date is an example of such constraint. Problem detection INFORMATION_SCHEMA only 2025-11-07 10:11 MIT License View
522 INFORMATION_SCHEMA is missing Make sure that you do not drop INFORMATION_SCHEMA schema. In this case most of the design checking queries will not work. This schema automatically exists in all databases. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
523 Input parameters that names do not follow the convention to start with _ or p_ For the sake of making code better understandable follow naming conventions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
524 Input parameters with the same name have different types This query analyzes the semantic consistency of routine signatures by identifying named input parameters that share an identical identifier but possess divergent data types across different routines. While this pattern is a prerequisite for valid routine overloading (polymorphism), it can also indicate a lack of standardization in the data dictionary. For example, using the parameter name status to denote an INTEGER in one context and TEXT in another creates ambiguity regarding the parameter's expected domain. The query results should be audited to distinguish intentional overloading from naming inconsistencies. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-15 11:34 MIT License View
525 Insufficient number of user-defined base tables This query performs a basic structural assessment of the database schema by counting the number of user-defined base tables. It verifies whether the data model meets a minimum complexity threshold, requiring the existence of at least 7 distinct base tables. This metric serves as a proxy for the scope and depth of the implemented domain model. Problem detection INFORMATION_SCHEMA only 2025-11-30 09:30 MIT License View
526 Insufficient number of user-defined domains This query assesses the utilization of user-defined domains within the database schema. It verifies a specific structural requirement: the database must contain at least one user-defined domain that is referenced by at least two distinct non-foreign key columns in base tables. This metric serves as an indicator of proper domain reuse and data type standardization. The query validates whether the schema design effectively leverages domains to enforce consistent data definitions across multiple attributes. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-20 11:47 MIT License View
527 Insufficient number of user-defined foreign tables This query assesses the utilization of PostgreSQL's Foreign Data Wrapper (FDW) capabilities. It verifies the existence of at least 2 user-defined foreign tables within the database schema. This requirement ensures that the implemented solution demonstrates the ability to integrate and query data from external sources, extending the data model beyond local storage. Problem detection INFORMATION_SCHEMA only 2025-11-30 09:31 MIT License View
528 Insufficient number of user-defined non-trigger routines This query performs a quantity-based assessment of the database's procedural logic. It verifies whether the schema meets a minimum complexity requirement by counting the number of user-defined, non-trigger routines (functions and procedures). The check passes only if the count is equal to or greater than the threshold of 4. This metric is used to ensure a baseline level of backend logic implementation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-30 08:47 MIT License View
529 Insufficient number of user-defined non-trigger SQL routines with SQL-standard routine body This query assesses the database's adherence to modern SQL standards regarding procedural code. It verifies whether the schema contains a minimum of 3 user-defined, non-trigger routines that are specifically written in the SQL language and utilize a SQL-standard body (defined using BEGIN ATOMIC ... END). This excludes routines written in PL/pgSQL or those using the legacy string-literal body definition. The metric ensures a baseline adoption of the standardized, portable syntax for SQL routines introduced in newer PostgreSQL versions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-30 09:22 MIT License View
530 Insufficient number of user-defined triggers+rules This query assesses the extent of active logic implementation within the database schema. It verifies that there are at least 3 user-defined triggers or rewrite rules present in the database. These objects represent automated tasks that for example enforce complex integrity constraints. Meeting this threshold indicates a baseline competency in utilizing PostgreSQL's event-driven capabilities. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-30 09:36 MIT License View
531 Insufficient number of user-defined triggers+rules (based on number of tasks) This query assesses the extent of active logic implementation within the database schema. It verifies that there are user-defined triggers or rewrite rules present for at least 3 tasks. These objects represent automated tasks that for example enforce complex integrity constraints. Meeting this threshold indicates a baseline competency in utilizing PostgreSQL's event-driven capabilities. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-30 09:34 MIT License View
532 Insufficient number of user-defined views This query assesses the use of abstraction layers within the database schema. It verifies that there are at least 4 user-defined views present. Views are essential for simplifying complex queries, restricting data access, and presenting specific data perspectives to applications. Meeting this threshold indicates a sufficient implementation of data abstraction and query encapsulation. Problem detection INFORMATION_SCHEMA only 2025-11-30 09:38 MIT License View
533 Insufficient routine privileges You must give rights to use routines to the users/roles that correspond to applications. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
534 Insufficient view privileges You must give privileges to use views to the users/roles that correspond to applications Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
535 Intra-object inconsistency in string concatenation methods This query detects internal inconsistency within individual database objects (user-defined routines, views, materialized views). It flags objects that utilize both the standard concatenation operator (||) and variadic concatenation functions (concat() or concat_ws()) within the same definition body. Mixing null-unsafe operators (||) with null-safe functions (concat) in a single routine suggests a lack of coherent logic or an incomplete refactoring effort, potentially leading to confusing behavior regarding NULL handling. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-27 12:27 MIT License View
536 Invalid character class PostgreSQL regular expressions do not have character classes word and letter. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
537 Invalid explicit locking with aggregate functions This query identifies SQL statements that attempt to apply explicit row locking (e.g., FOR SHARE, FOR UPDATE) to the result of an aggregate function (e.g., COUNT(*)). This is a semantic error because locking clauses operate on specific physical rows, whereas aggregate functions return a derived scalar value that is decoupled from the underlying row versions. To correctly enforce a lock, the query must select the specific columns (typically the primary key) of the target rows, rather than a computed aggregate. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-10 12:59 MIT License View
538 Invalid use of the case insensitive search modifier in regular expressions Find regular expression patterns that use (?i) modifier in any other place than at the beginning of the pattern or (?-i) in any place of the pattern. Such use of the modifiers is not supported by PostgreSQL. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
539 Invocation of a system-defined routine without providing any arguments Find user-defined routines that contain an invocation of a system-defined function without providing any argument. The query considers all aggregate functions as well as some popular scalar functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
540 Invocation of PL/pgSQL functions from the subqueries of derived tables Find derived tables that subqueries invoke a PL/pgSQL function. Avoid context switch that is caused by the invocation of PL/pgSQL functions from the subqueries of derived tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View