Feature Description

Support option to treat nulls as non-distinct in unique indexes and constraints within postgres databases. This is useful for enforcing multi-column unique indexes and constraints can't have duplicate entires even if one or more columns are nullable.

PostgreSQL Documentation:

By default, NULL values are not treated as distinct entries. Specifying NULLS NOT DISTINCT on unique indexes / constraints will cause NULL values to be treated distinctly. - Feature Description

Unique Constraints
Create Index

The Solution

Decorators for unique index and constraint have nullsNotDistinct option (default: false) applicable to pg15 and above only.

Add nullsNotDistinct option to Unique decorator.

interface UniqueOptions {
    /**
     * By default, NULL values are not treated as distinct entries. 
     * Specifying NULLS NOT DISTINCT on unique constraints 
     * will cause NULL values to be treated distinctly.
     *
     * Works only in PostgreSQL 15 and above.
     */
    nullsNotDistinct?: boolean

Add nullsNotDistinct option to Index decorator.

interface IndexOptions {
    /**
     * Indicates if this composite index must be unique or not.
     */
    unique?: boolean
    ...
    /**
     * By default, NULL values are not treated as distinct entries. 
     * Specifying NULLS NOT DISTINCT on unique indexes 
     * will cause NULL values to be treated distinctly.
     * 
     * Only applicable when unique: true.
     * Works only in PostgreSQL 15 and above.
     */
    nullsNotDistinct?: boolean
}

Considered Alternatives

Alternatives

• add NULLS NOT DISTINCT to migrations manually
• modify migrations to use COALESCE to map nulls to a single value

Both alternatives do not work with synchronization since they require modifying migrations

Additional Context

No response

Relevant Database Driver(s)

• aurora-mysql
• aurora-postgres
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• spanner
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.