How to correctly do upsert in postgres 9.5

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  

As as 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;