Filter Queries

Found 1077 queries.

Page 24 of 54
  • 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 the relatively small amount of data in the system catalog of a database.

#461. Incorrect prefix of a constraint name or an index name

INFORMATION_SCHEMA+system catalog base tables

If the name of an object has the prefix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find prefixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use chk_ as the prefix of an index name or pk_ as the prefix of a check constraint name.

Problem detection License: MIT (opens in new tab)

#462. Incorrect reference to a system-defined function in the routine body

INFORMATION_SCHEMA+system catalog base tables

Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp.

Problem detection License: MIT (opens in new tab)

#463. Incorrect specification of logical or in regular expressions

INFORMATION_SCHEMA+system catalog base tables

Find the use of regular expressions where logical or is incorrectly specified, i.e., (| or |).

Problem detection License: MIT (opens in new tab)

#464. Incorrect suffix of a constraint name or an index name

INFORMATION_SCHEMA+system catalog base tables

If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name.

Problem detection License: MIT (opens in new tab)

#465. Incorrect use of COUNT(*) with outer joins

INFORMATION_SCHEMA+system catalog base tables

This query identifies user-defined routines and derived tables (views or materialized views) that use the COUNT(*) aggregate function (without a FILTER clause) alongside an OUTER JOIN and a GROUP BY clause. This combination is a common SQL anti-pattern. When grouping with an outer join, COUNT(*) counts the rows themselves, including the NULL-filled rows generated for non-matching records. Consequently, it inaccurately reports a count of 1 for completely empty groups instead of the correct count of 0. To get accurate results, developers should count a specific non-null column from the joined table.

Problem detection License: MIT (opens in new tab)

#466. Indexes using upper() instead of lower()

INFORMATION_SCHEMA+system catalog base tables

This query identifies expression indexes that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive indexing and comparisons. Enforcing this convention ensures consistency across the schema and prevents performance issues caused by queries failing to utilize indexes (for example, if a developer queries using lower(col) but the index is built on upper(col)).

Problem detection License: MIT (opens in new tab)

#467. INFORMATION_SCHEMA is missing

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#468. Input parameters that names do not follow the convention to start with _ or p_

INFORMATION_SCHEMA+system catalog base tables

For the sake of making code better understandable follow naming conventions.

Problem detection License: MIT (opens in new tab)

#469. Input parameters with the same name have different types

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#470. Insufficient number of user-defined domains

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#471. Insufficient number of user-defined non-trigger routines

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#472. Insufficient number of user-defined non-trigger SQL routines with SQL-standard routine body

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#473. Insufficient number of user-defined triggers+rules

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#474. Insufficient number of user-defined triggers+rules (based on number of tasks)

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#475. Intra-object inconsistency in string concatenation methods

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#476. Invalid character class

INFORMATION_SCHEMA+system catalog base tables

PostgreSQL regular expressions do not have character classes word and letter.

Problem detection License: MIT (opens in new tab)

#477. Invalid explicit locking with aggregate functions

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#478. Invalid or generic error codes in exceptions

INFORMATION_SCHEMA+system catalog base tables

This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and have malformed, generic, or improperly classified ERRCODE values in RAISE EXCEPTION statements. It acts as a linter for PostgreSQL error handling best practices. Specifically, it flags generic codes (like P0001 or raise_exception), the misuse of warning/info classes (00-02) as breaking exceptions, and the hijacking of reserved standard error classes. It also ensures that custom error codes follow the correct 5-character format and start with a digit between 5 and 9, thereby preventing conflicts with built-in PostgreSQL errors.

Problem detection License: MIT (opens in new tab)

#479. Invalid use of the case insensitive search modifier in regular expressions

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)

#480. Invocation of a system-defined routine without providing any arguments

INFORMATION_SCHEMA+system catalog base tables

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 License: MIT (opens in new tab)
# Name(activate to sort ascending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(sorted ascending, activate to sort descending) Last update(activate to sort ascending) License Actions
461 Incorrect prefix of a constraint name or an index name If the name of an object has the prefix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find prefixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use chk_ as the prefix of an index name or pk_ as the prefix of a check constraint name. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
462 Incorrect reference to a system-defined function in the routine body Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
463 Incorrect specification of logical or in regular expressions Find the use of regular expressions where logical or is incorrectly specified, i.e., (| or |). Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
464 Incorrect suffix of a constraint name or an index name If the name of an object has the suffix that refers to the type of the object (for instance, primary key constraint or foreign key constraint), then you should use references to the correct object type. Find suffixes of constraint names and index names that incorrectly refer to the type of the object. For instance, incorrect would be to use _chk as the suffix of an index name or _pk as the suffix of a check constraint name. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
465 Incorrect use of COUNT(*) with outer joins This query identifies user-defined routines and derived tables (views or materialized views) that use the COUNT(*) aggregate function (without a FILTER clause) alongside an OUTER JOIN and a GROUP BY clause. This combination is a common SQL anti-pattern. When grouping with an outer join, COUNT(*) counts the rows themselves, including the NULL-filled rows generated for non-matching records. Consequently, it inaccurately reports a count of 1 for completely empty groups instead of the correct count of 0. To get accurate results, developers should count a specific non-null column from the joined table. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
466 Indexes using upper() instead of lower() This query identifies expression indexes that apply the upper() function to a column. In PostgreSQL, it is a strong community convention to standardize on the lower() function for case-insensitive indexing and comparisons. Enforcing this convention ensures consistency across the schema and prevents performance issues caused by queries failing to utilize indexes (for example, if a developer queries using lower(col) but the index is built on upper(col)). Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
467 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 MIT (opens in new tab) View
468 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 MIT (opens in new tab) View
469 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 MIT (opens in new tab) View
470 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 MIT (opens in new tab) View
471 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 MIT (opens in new tab) View
472 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 MIT (opens in new tab) View
473 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 MIT (opens in new tab) View
474 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 MIT (opens in new tab) View
475 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 MIT (opens in new tab) View
476 Invalid character class PostgreSQL regular expressions do not have character classes word and letter. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
477 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 MIT (opens in new tab) View
478 Invalid or generic error codes in exceptions This query identifies PL/pgSQL routines that invoke RAISE EXCEPTION and have malformed, generic, or improperly classified ERRCODE values in RAISE EXCEPTION statements. It acts as a linter for PostgreSQL error handling best practices. Specifically, it flags generic codes (like P0001 or raise_exception), the misuse of warning/info classes (00-02) as breaking exceptions, and the hijacking of reserved standard error classes. It also ensures that custom error codes follow the correct 5-character format and start with a digit between 5 and 9, thereby preventing conflicts with built-in PostgreSQL errors. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
479 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 MIT (opens in new tab) View
480 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 MIT (opens in new tab) View