How to setup unique constraint with soft delete enabled

[talgat-ruby]

Basically with Postgres NULL != NULL. And adding

( name, surname, deleted_at ) - person_name_surname_deleted_at_key

won't work.

In raw query uniqueness can be achieved with

(name, surname, COALESCE(deleted_at, ''))

Is there workaround for Hasura?

[jdinartejesus]

@talgat-ruby did you found a way?

[talgat-ruby]

Hey @jdinartejesus,

If you are using postgres 15 or newer you can use new constraint UNIQUE NULLS NOT DISTINCT. If not than it is possible only via workaround like not using null.

More on stackoverflow question.

[jdinartejesus]

Worked! Thanks so much for the quick reply!