How to correctly do upsert in postgres 9.5

Question

correct syntax of upsert with postgresql 9.5, below query shows column reference "gallery_id" is ambiguous error , why?

var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

I tried change WHERE gallery_id = $2; to WHERE category_gallery.gallery_id = $2; then shows error there is no unique or exclusion constraint matching the ON CONFLICT specification, but I don't want to set gallery_id or category_id as unique becuase I want to sure both column are same then do update....

How to correctly do upsert in postgres 9.5?

if ON CONFLICT need unique column, should I use other method, how?

I want to sure multiple column both conflict then do update, what is correct usage

var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;


var dbQuery = `INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id AND gallery_id)
  DO UPDATE SET
  category_id = $1,
  last_modified_date = $3,
  last_modified_by_user_id = $4
  WHERE gallery_id = $2`;

table (category_id , gallery_id not unique column)

category_id | gallery_id | create_date | create_by_user_id | last_modified_date | last_modified_by_user_id
1 | 1 | ...  
1 | 2 | ...
2 | 2 | ...
1 | 3 | ...

Embowel

Answer 1

The ON CONFLICT construct requires a UNIQUE constraint to work. From the documentation on INSERT .. ON CONFLICT clause:

The optional ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error. For each individual row proposed for insertion, either the insertion proceeds, or, if an arbiter constraint or index specified by conflict_target is violated, the alternative conflict_action is taken. ON CONFLICT DO NOTHING simply avoids inserting a row as its alternative action. ON CONFLICT DO UPDATE updates the existing row that conflicts with the row proposed for insertion as its alternative action.

Now, the question is not very clear but you probably need a UNIQUE constraint on the 2 columns combined: (category_id, gallery_id).

ALTER TABLE category_gallery
    ADD CONSTRAINT category_gallery_uq
    UNIQUE (category_id, gallery_id) ;

If the row to be inserted matches both values with a row already on the table, then instead of INSERT, do an UPDATE:

INSERT INTO category_gallery (
  category_id, gallery_id, create_date, create_by_user_id
  ) VALUES ($1, $2, $3, $4)
  ON CONFLICT (category_id, gallery_id)
  DO UPDATE SET
    last_modified_date = EXCLUDED.create_date,
    last_modified_by_user_id = EXCLUDED.create_by_user_id ;

You can use either the columns of the UNIQUE constraint:

  ON CONFLICT (category_id, gallery_id) 

or the constraint name:

  ON CONFLICT ON CONSTRAINT category_gallery_uq  

Marzipan

thanks for reply , but I don't want category_id or gallery_id be unique column, mention in question. I want sure if category_id and gallery_id both same then create new row, please see my update date example in question – Embowel

yes if already exists category_id=1 and gallery_id=3 then update, not insert. I use your script, if I want to update category_id = 2 where gallery_id = 3 not working. or should I add another update script ? – Embowel

I also tried this INSERT INTO category_gallery ( category_id, gallery_id, create_date, create_by_user_id ) VALUES ($1, $2, $3, $4) ON CONFLICT (category_id, gallery_id) DO UPDATE SET category_id = $1, last_modified_date = EXCLUDED.create_date, last_modified_by_user_id = EXCLUDED.create_by_user_id WHERE category_gallery.gallery_id = $2 – Embowel

I don't follow what you mean with update category_id = 2 where gallery_id = 3. Do you want to insert/update a row with category_id = 2 and gallery_id = 3? I don't have a WHERE anywhere in my script. – Against

@Marzipan I've solved the problem thanks to your answer, but my issues is that calling ON CONFLICT CONSTRAINT category_gallery_uq by constraint name is incorrect and will cause syntax error, please kindly consider updating your answer by changing it to ON CONFLICT ON CONSTRAINT category_gallery_uq . Big thank – Dukedom

upvoted! any way to do on conflict do update all columns without having to mention each – Systematology

Answer 2

As a simplified alternative to the currently accepted answer, the UNIQUE constraint can be anonymously added upon creation of the table:

CREATE TABLE table_name (
    id  TEXT PRIMARY KEY,
    col TEXT,
    UNIQUE (id, col)
);

Then, the upsert query becomes (similar to what was already answered):

INSERT INTO table_name (id, col) VALUES ($1, $2)
ON CONFLICT (id, col)
    DO UPDATE SET col = $2;

Drive

Answer 3

It is not for the Postgres' version asked, but someone might find interesting to check the great MERGE feature available in PostgreSQL V15 and newer.

MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

Abelmosk