Im trying to use typeorm softdelete feature , for deleting its fine ,adds a timestamps to deletedAt field but the problem emerges when you have unique field like "username" and you softdeleted it then trying to add another record with the same "username" field value as deleted record . it doesn't accept it ,since its already inserted once and database has no perception of the softdelete . so i tried to add another field making it a composite unique constraint combining "username" with "deletedAt" , in Postgres it seems like Nulls are not being indexed as i expected . so you again might add multiple records with same "username" and same "NULL" value for deletedAt . trying to bypass this by assigning a default value of 0 to deletedAt and now i assume the main issue is how to tell typeorm treat 0 same as it treated NULL in getting queries.
You could try a partial index
CREATE UNIQUE INDEX ix_username ON usertable (username)
WHERE (deletedAt is null);