CODEWITHSUNDEEP
postgresqlunique-constraint
sojim2
sojim2

Reputation: 1307

PostgreSQL: How to get ADD UNIQUE to work

I've truncated my table then added unique:

ALTER TABLE mytable
ADD UNIQUE (id,loan_type,term,oldestyear)

After inserting data, it still duplicates, did I do something wrong?

id       |loan_type      |apr   |term|oldestyear|valid_period                     
---------|---------------|------|----|----------|---------------------------------
     8333|auto new       |0.0249|  36|          |["2019-02-26 22:48:07.305304-08",
     8333|auto new       |0.0249|  36|          |["2019-02-26 22:47:38.421624-08",

I want id,loan_type,term, & oldest year to be a unique set. oldestyear will be null sometimes but it's still a unique set despite that.

Upvotes: 0

Views: 41

Answers (1)

user330315
user330315

Reputation:

If you can find at least one invalid (not null) value, then you can create a unique index on an expression that converts the NULL value to a non-null value so that the comparison treats them as identical:

create unique index on mytable (id,loan_type,term,coalesce(oldestyear, -42));

Upvotes: 2

Related Questions