Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 | ... 
like image 743
user1575921 Avatar asked Apr 22 '16 16:04

user1575921


People also ask

How does upsert work in PostgreSQL?

In relational databases, the term upsert is referred to as merge. The idea is that when you insert a new row into the table, PostgreSQL will update the row if it already exists, otherwise, it will insert the new row. That is why we call the action is upsert (the combination of update or insert).

How do you insert if row does not exist upsert in PostgreSQL?

There is a nice way of doing conditional INSERT in PostgreSQL: INSERT INTO example_table (id, name) SELECT 1, 'John' WHERE NOT EXISTS ( SELECT id FROM example_table WHERE id = 1 );

What is upsert operation?

The term upsert is a portmanteau – a combination of the words “update” and “insert.” In the context of relational databases, an upsert is a database operation that will update an existing row if a specified value already exists in a table, and insert a new row if the specified value doesn't already exist.

What is upsert statement?

The UPSERT (or REPLACE) statement with a subquery operates like the INSERT statement. The exception with this command is that, if an existing row in the table has the same primary key value as a new row, the row is updated with the returned record from the subquery.


Video Answer


2 Answers

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   
like image 165
ypercubeᵀᴹ Avatar answered Sep 23 '22 18:09

ypercubeᵀᴹ


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; 
like image 37
Erik Avatar answered Sep 21 '22 18:09

Erik