CODEWITHSUNDEEP
postgresqlupsertpostgresql-10
sojim2
sojim2

Reputation: 1307

PostgreSQL 10: How to add unique with constraint and default value -1

I'm looking to add unique values with constraint and with a default value of -1 to term and oldestyear:

ALTER TABLE live_mytable
    ADD CONSTRAINT live_mytable_uq
    UNIQUE (id,loan_type,coalesce(term,-1),coalesce(oldestyear, -1));

Because my goal is to be able to upsert -- insert if set doesn't exist and update the apr column if there's a conflict (if set exists but apr value is different):

INSERT INTO live_mytable(id, loan_type, apr, term, oldestyear) 
SELECT id, loan_type, apr, term, oldestyear
FROM   imp_mytable
  ON CONFLICT (id,loan_type,term,oldestyear) 
  DO UPDATE SET
     apr = excluded.apr;

The reason why I need a default value -1 is because unique doesn't recognize null values, and columns term and oldestyear can be null.

I'm new to PostgreSQL so I'm not sure if my approach is correct.

My table design: enter image description here

id       |loan_type      |apr   |term|oldestyear|
---------|---------------|------|----|----------|
    68459|auto new       |  3.50|  48|          |
    68459|auto new       |  3.50|  60|          |
    68459|auto new       |  3.99|  72|          |
    68459|auto used      |  3.24|  36|          |
    68459|auto used      |  3.74|  48|          |
    68459|auto used      |  3.74|  60|          |
    68459|auto used      |  3.99|  72|          |
    10066|auto new       |  2.99|  36|          |
    10066|auto new       |  3.29|  48|          |

My question is, how can I upsert from imp_mytable to live_mytable given that term and oldestyear can be null. And only update if apr is different when the unique set matches.

Upvotes: 1

Views: 1674

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247475

With your current data model you cannot, unless you do it the old way with a loop in PL/pgSQL that tries both INSERT and UPDATE until one of them succeeds.

Maybe you can use a value other than NULL to denote missing values in your columns, like -1. That would make it easy, and it might also simplify some of your queries.

Upvotes: 1

Related Questions