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_…

Accepted 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 ON CONSTRAINT category_gallery_uq  
```

How to correctly do upsert in postgres 9.5

Solution

Problem