| Goal | 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. |
|---|---|
| Notes | Refers to the column pg_index.indnullsnotdistinct and thus works starting from PostgreSQL 15. |
| Type | Problem detection Each row in the result could represent a flaw in the design |
| Reliability | Medium Medium number of false-positive results |
| License | MIT (opens in new tab) |
| Data Source | INFORMATION_SCHEMA+system catalog |
| SQL Query |
|
Fixing Suggestion
To resolve this issue and clean up the database schema, drop the redundant constraint or index and recreate it without the NULLS NOT DISTINCT clause. A standard UNIQUE constraint is perfectly sufficient and logically clearer for mandatory columns.
Collections
This query belongs to the following collections:
Find problems automatically
Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
| Name | Description |
|---|---|
| Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Categories
This query is classified under the following categories:
Missing data
Queries of this category provide information about missing data (NULLs) in a database.
Uniqueness
Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes.
| Name | Description |
|---|---|
| Missing data | Queries of this category provide information about missing data (NULLs) in a database. |
| Uniqueness | Queries of this category provide information about uniqueness constraints (PRIMARY KEY, UNIQUE, EXCLUDE) as well as unique indexes. |