Filter Queries

Found 1092 queries.

Page 30 of 55
  • 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.

#581. Non-key and non-foreign key base table columns with the same name and type that have in some cases permit NULLs and in some cases not

INFORMATION_SCHEMA+system catalog base tables

Find non-key and non-foreign key base table columns with the same name and type that in some cases permit NULLs and in some cases not. Be consistent. Make sure that this selection is consistent.

Problem detection License: MIT (opens in new tab)

#582. Non-native data types for network addresses

INFORMATION_SCHEMA only

This query identifies base table columns intended to store IP or network addresses that are not using PostgreSQL's native inet data type. It locates these columns by searching for specific English and Estonian naming patterns (such as 'ip', 'network', 'addr', or 'aadr'). Storing network addresses as generic text is an anti-pattern, as the inet type provides built-in validation, specialized network functions, and efficient indexing.

Problem detection License: MIT (opens in new tab)

#583. Non-predefined character classes must not be between double square brackets

INFORMATION_SCHEMA+system catalog base tables

Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'.

Problem detection License: MIT (opens in new tab)

#584. Non-standard data types for surrogate key columns (based on column names)

INFORMATION_SCHEMA+system catalog base tables

This query identifies base table columns participating in a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint whose names suggest they store surrogate key values (having an id prefix or suffix), yet are defined with a data type other than an integer type (smallint, integer, bigint) or uuid. Surrogate keys are system-generated identifiers carrying no business meaning and should be stored using compact, fixed-width integer types or native uuid values. Storing surrogate keys as text (varchar, text) or numeric types increases storage overhead, introduces unnecessary string collation costs, and slows down index lookups and table joins.

Problem detection License: MIT (opens in new tab)

#585. Non-updatable views that have data modification privileges

INFORMATION_SCHEMA+system catalog base tables

Be precise and do not give impossible privileges.

Problem detection License: MIT (opens in new tab)

#586. No-operation routines with static return values

INFORMATION_SCHEMA+system catalog base tables

This query identifies SQL routines that are functionally equivalent to a no-operation (no-op) instruction, meaning their sole operation is to return either a constant literal or an unmodified input parameter. Such routines provide no transformation or logic. They are typically superfluous and may represent placeholder code from early development, refactoring artifacts where original logic was deprecated, or simple logical oversights. Eliminating these functions reduces code clutter, simplifies application logic, and removes a marginal but unnecessary layer of computational overhead.

Problem detection License: MIT (opens in new tab)

#587. No point to have in a procedure COMMIT without ROLLBACK or vice versa

INFORMATION_SCHEMA+system catalog base tables

If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11.

Problem detection License: MIT (opens in new tab)

#588. Not enforced constraints

system catalog base tables only

This query identifies constraints (CHECK and FOREIGN KEY) that exist in the system catalog but are not actively enforced against the table data.

Problem detection License: MIT (opens in new tab)

#589. Not equals check in unstandardized way

INFORMATION_SCHEMA+system catalog base tables

Find user-defined routines that use != operator to test as to whether two values are not equal.

Problem detection License: MIT (opens in new tab)

#590. Not inherited CHECK constraints

system catalog base tables only

Find CHECK constraints that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. CHECK(false) on a supertable is an appropriate CHECK constraint if one wants to prevent registering data directly to the supertable, i.e., data can only be added to the subtables.

General License: MIT (opens in new tab)

#591. Not inherited CHECK constraints that are recreated in the immediate subtable

system catalog base tables only

Find base table CHECK constraints that have been defined as NOT INHERITED but the constraint with the same Boolean expression has been defined in the immediate subtable of the table.

Problem detection License: MIT (opens in new tab)

#592. Not inherited CHECK constraints that cover at least one column

system catalog base tables only

Find CHECK constraints that cover at least one column and that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable.

Problem detection License: MIT (opens in new tab)

#593. NOT IN or <> ALL in derived tables

INFORMATION_SCHEMA+system catalog base tables

Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor, especially in case of large data sizes.

Problem detection License: MIT (opens in new tab)

#594. NOT IN or <> ALL in routines

INFORMATION_SCHEMA+system catalog base tables

Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor.

Problem detection License: MIT (opens in new tab)

#595. NOT NULL constraint is directly associated with a column instead of the domain of the column

INFORMATION_SCHEMA+system catalog base tables

Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments.

Problem detection License: MIT (opens in new tab)

#596. NOT NULL constraint via CHECK instead of NOT NULL constraint

INFORMATION_SCHEMA only

This query identifies columns where non-nullability is enforced using an explicit CHECK constraint (e.g., CHECK (column_name IS NOT NULL)) rather than a standard NOT NULL column constraint. Although PostgreSQL internally processes NOT NULL constraints in a manner similar to CHECK constraints, relying on explicit CHECK constraints for this purpose violates standard SQL conventions. Enforcing non-nullability consistently via the dedicated NOT NULL constraint improves schema readability, ORM compatibility, and overall design uniformity.

Problem detection License: MIT (opens in new tab)

#597. NOT NULL domains

INFORMATION_SCHEMA+system catalog base tables

Find domains with NOT NULL constraints and base table columns that have been defined based on the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments.

General License: MIT (opens in new tab)

#598. NOT VALID foreign key constraints

system catalog base tables only

Find not valid foreign key constraints. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint.

Problem detection License: MIT (opens in new tab)

#599. Nullable timestamps missing an infinity default

INFORMATION_SCHEMA only

This query identifies optional (nullable) timestamp columns in base tables that lack a default value. It highlights fields that might represent open-ended time periods (such as expiration or end dates). In such cases, it is often a better practice to assign the special value 'infinity' as the default, rather than relying on NULL values.

Problem detection License: MIT (opens in new tab)

#600. NULLS NOT DISTINCT restriction on unique keys on mandatory columns

INFORMATION_SCHEMA+system catalog base tables

This query identifies unique constraints and indexes configured with the NULLS NOT DISTINCT property where all involved columns are mandatory (NOT NULL). Using this modifier on strictly mandatory columns is completely redundant. Since the columns cannot contain NULL values in the first place, the special null-handling logic serves no purpose and only unnecessarily clutters the database schema.

Problem detection License: MIT (opens in new tab)
# Name(sorted ascending, activate to sort descending) Goal(activate to sort ascending) Type(activate to sort ascending) Data source(activate to sort ascending) Last update(activate to sort ascending) License Actions
581 Non-key and non-foreign key base table columns with the same name and type that have in some cases permit NULLs and in some cases not Find non-key and non-foreign key base table columns with the same name and type that in some cases permit NULLs and in some cases not. Be consistent. Make sure that this selection is consistent. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
582 Non-native data types for network addresses This query identifies base table columns intended to store IP or network addresses that are not using PostgreSQL's native inet data type. It locates these columns by searching for specific English and Estonian naming patterns (such as 'ip', 'network', 'addr', or 'aadr'). Storing network addresses as generic text is an anti-pattern, as the inet type provides built-in validation, specialized network functions, and efficient indexing. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
583 Non-predefined character classes must not be between double square brackets Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
584 Non-standard data types for surrogate key columns (based on column names) This query identifies base table columns participating in a PRIMARY KEY, UNIQUE, or FOREIGN KEY constraint whose names suggest they store surrogate key values (having an id prefix or suffix), yet are defined with a data type other than an integer type (smallint, integer, bigint) or uuid. Surrogate keys are system-generated identifiers carrying no business meaning and should be stored using compact, fixed-width integer types or native uuid values. Storing surrogate keys as text (varchar, text) or numeric types increases storage overhead, introduces unnecessary string collation costs, and slows down index lookups and table joins. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
585 Non-updatable views that have data modification privileges Be precise and do not give impossible privileges. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
586 No-operation routines with static return values This query identifies SQL routines that are functionally equivalent to a no-operation (no-op) instruction, meaning their sole operation is to return either a constant literal or an unmodified input parameter. Such routines provide no transformation or logic. They are typically superfluous and may represent placeholder code from early development, refactoring artifacts where original logic was deprecated, or simple logical oversights. Eliminating these functions reduces code clutter, simplifies application logic, and removes a marginal but unnecessary layer of computational overhead. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
587 No point to have in a procedure COMMIT without ROLLBACK or vice versa If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
588 Not enforced constraints This query identifies constraints (CHECK and FOREIGN KEY) that exist in the system catalog but are not actively enforced against the table data. Problem detection system catalog base tables only MIT (opens in new tab) View
589 Not equals check in unstandardized way Find user-defined routines that use != operator to test as to whether two values are not equal. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
590 Not inherited CHECK constraints Find CHECK constraints that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. CHECK(false) on a supertable is an appropriate CHECK constraint if one wants to prevent registering data directly to the supertable, i.e., data can only be added to the subtables. General system catalog base tables only MIT (opens in new tab) View
591 Not inherited CHECK constraints that are recreated in the immediate subtable Find base table CHECK constraints that have been defined as NOT INHERITED but the constraint with the same Boolean expression has been defined in the immediate subtable of the table. Problem detection system catalog base tables only MIT (opens in new tab) View
592 Not inherited CHECK constraints that cover at least one column Find CHECK constraints that cover at least one column and that have been defined in a supertable (parent table) but not in its subtables. An entity that belongs to a subtype should also belong to its supertype. If a subtype entity satisfies some constraint, then logically it must also satisfy the constraints of the supertype as well. If CHECK constraints are not inherited, then this is not guaranteed. If you implement subtyping not merely reuse implementation in the subtables, then the subtables must have at least the same CHECK constraints as the supertable. Problem detection system catalog base tables only MIT (opens in new tab) View
593 NOT IN or <> ALL in derived tables Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor, especially in case of large data sizes. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
594 NOT IN or <> ALL in routines Avoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
595 NOT NULL constraint is directly associated with a column instead of the domain of the column Find mandatory (NOT NULL) base table columns that have been defined based on the same domain but the NOT NULL constraint is associated directly with the column not to the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
596 NOT NULL constraint via CHECK instead of NOT NULL constraint This query identifies columns where non-nullability is enforced using an explicit CHECK constraint (e.g., CHECK (column_name IS NOT NULL)) rather than a standard NOT NULL column constraint. Although PostgreSQL internally processes NOT NULL constraints in a manner similar to CHECK constraints, relying on explicit CHECK constraints for this purpose violates standard SQL conventions. Enforcing non-nullability consistently via the dedicated NOT NULL constraint improves schema readability, ORM compatibility, and overall design uniformity. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
597 NOT NULL domains Find domains with NOT NULL constraints and base table columns that have been defined based on the domain. PostgreSQL CREATE DOMAIN statement documentation points out that it is possible to add NULL's to columns that have a NOT NULL domain and thus suggests to associate NOT NULL constraints with a column instead of the domain. However, this is a non-standard behavior and defeats the idea of domain as a reusable asset. The scenarios where NULLs can appear in columns with a NOT NULL domain are quite exotic and probably cannot appear in production environments. General INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View
598 NOT VALID foreign key constraints Find not valid foreign key constraints. These constraints have been created so that the existing data has not been checked against the constraint. It could be deliberate in case of legacy systems that have data quality problems. However, ideally all the data in the table conforms to the constraint. Problem detection system catalog base tables only MIT (opens in new tab) View
599 Nullable timestamps missing an infinity default This query identifies optional (nullable) timestamp columns in base tables that lack a default value. It highlights fields that might represent open-ended time periods (such as expiration or end dates). In such cases, it is often a better practice to assign the special value 'infinity' as the default, rather than relying on NULL values. Problem detection INFORMATION_SCHEMA only MIT (opens in new tab) View
600 NULLS NOT DISTINCT restriction on unique keys on mandatory columns This query identifies unique constraints and indexes configured with the NULLS NOT DISTINCT property where all involved columns are mandatory (NOT NULL). Using this modifier on strictly mandatory columns is completely redundant. Since the columns cannot contain NULL values in the first place, the special null-handling logic serves no purpose and only unnecessarily clutters the database schema. Problem detection INFORMATION_SCHEMA+system catalog base tables MIT (opens in new tab) View