How to correctly do upsert in postgres 9.5
<p>correct syntax of upsert with postgresql 9.5, below query shows <code>column reference "gallery_id" is ambiguous</code> error , why?</p>

<pre><code>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`;
</code></pre>

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

<p>How to correctly do upsert in postgres 9.5?  </p>

<p>if <code>ON CONFLICT</code> need unique column, should I use other method, how?  </p>

<p><br><br></p>

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

<pre><code>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`;
</code></pre>

<p>table  (category_id , gallery_id not unique column)</p>

<pre><code>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 | ...
</code></pre>