Filter 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 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 tablesFind 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.
#582. Non-native data types for network addresses
INFORMATION_SCHEMA onlyThis 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.
#583. Non-predefined character classes must not be between double square brackets
INFORMATION_SCHEMA+system catalog base tablesWrite 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]]+$'.
#584. Non-standard data types for surrogate key columns (based on column names)
INFORMATION_SCHEMA+system catalog base tablesThis 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.
#585. Non-updatable views that have data modification privileges
INFORMATION_SCHEMA+system catalog base tablesBe precise and do not give impossible privileges.
#586. No-operation routines with static return values
INFORMATION_SCHEMA+system catalog base tablesThis 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.
#587. No point to have in a procedure COMMIT without ROLLBACK or vice versa
INFORMATION_SCHEMA+system catalog base tablesIf 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.
#588. Not enforced constraints
system catalog base tables onlyThis query identifies constraints (CHECK and FOREIGN KEY) that exist in the system catalog but are not actively enforced against the table data.
#589. Not equals check in unstandardized way
INFORMATION_SCHEMA+system catalog base tablesFind user-defined routines that use != operator to test as to whether two values are not equal.
#590. Not inherited CHECK constraints
system catalog base tables onlyFind 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.
#591. Not inherited CHECK constraints that are recreated in the immediate subtable
system catalog base tables onlyFind 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.
#592. Not inherited CHECK constraints that cover at least one column
system catalog base tables onlyFind 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.
#593. NOT IN or <> ALL in derived tables
INFORMATION_SCHEMA+system catalog base tablesAvoid 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.
#594. NOT IN or <> ALL in routines
INFORMATION_SCHEMA+system catalog base tablesAvoid using NOT IN or <>ALL with a non-correlated subquery in PostgreSQL because the query performance will be very poor.
#595. NOT NULL constraint is directly associated with a column instead of the domain of the column
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#596. NOT NULL constraint via CHECK instead of NOT NULL constraint
INFORMATION_SCHEMA onlyThis 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.
#597. NOT NULL domains
INFORMATION_SCHEMA+system catalog base tablesFind 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.
#598. NOT VALID foreign key constraints
system catalog base tables onlyFind 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.
#599. Nullable timestamps missing an infinity default
INFORMATION_SCHEMA onlyThis 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.
#600. NULLS NOT DISTINCT restriction on unique keys on mandatory columns
INFORMATION_SCHEMA+system catalog base tablesThis 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.
| # | 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 |