[Feature] Add "nulls [not] distinct" option to Postgres index config

[bdewilde]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-postgres functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Postgres indexes have many configurable parameters, while dbt-postgres's built-in indexes model configuration only supports a couple: columns, type, and unique. Previously, a feature request was made to support configurable names. I would like to have support for the NULLS [NOT] DISTINCT option:

Specifies whether for a unique index, null values should be considered distinct (not equal). The default is that they are distinct, so that a unique index could contain multiple null values in a column.

The implementation could have nulls_distinct=true as an arg with a default value, mirroring current (Postgres' default) behavior.

Describe alternatives you've considered

I've used dbt_utils.generate_surrogate_key, which handles nulls in a configurable manner. However, I've run into performance issues resulting from indexing on hashed values, rather than a nicely sorted composite index.

I've also implemented a custom macro for creating postgres indexes with additional parameters, but its usage is a bit hacky, and I don't really want to maintain that logic on my own.

Who will this benefit?

Users of dbt-postgres who need easy access to more of the functionality implemented by Postgres indexes. Specifically, in cases of unique indexes for which one of the columns has distinct, nullable values.

Are you interested in contributing this feature?

definitely maybe :)

Anything else?

No response

[bdewilde]

For what it's worth, this missing functionality just bit me once again. 😞

I'll check out your contributing guidelines and see if I can put together a PR.