user1575921 user1575921 - 7 months ago 72
SQL Question

How to correctly do upsert in postgres 9.5

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 | ...

Answer

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 CONSTRAINT category_gallery_uq  
Comments