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

Question

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:

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.

Laurenz Albe · Accepted Answer · 2019-02-28 07:25:17Z

1

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.

answered Feb 28, 2019 at 7:25

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

sojim2 Over a year ago

I've added default value -1::integer as seen in the picture, however, when I do the upsert function, it throws an error ERROR: pq: there is no unique or exclusion constraint matching the ON CONFLICT specification because there's no constraint. What constraint should I have along w/ my default value of -1 would work w/ that upsert function?

Laurenz Albe Over a year ago

Adding a DEFAULT value is not enough, you's actually have to update the table. Then you'd have to create the unique constraint as you wrote in your question (without the coalesce of course). Then it should work!

sojim2 Over a year ago

Although my column is set as default -1 why is it when I import, the null field remains null and not -1? table schema: i.imgur.com/E95xsdm.png table values: i.imgur.com/ykpZkhd.png

Laurenz Albe Over a year ago

Perhaps you explicitly insert NULL.

sojim2 Over a year ago

I thought adding default value -1 means the value of -1 would be added by default if column is null, I was mistaken, thanks!

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related