How to fix syntax error in postgresql UPSERT?

Question

I'm trying to learn postgresql's upsert syntax but I keep getting this error:

ERROR:  syntax error at or near "name"
LINE 5:     SET name = EXCLUDED.name;

this is the sql statement:

INSERT INTO users (name, fav_num) 
VALUES ('bob', 2) 
ON CONFLICT (id) DO UPDATE 
SET fav_num = EXCLUDED.fav_num, 
SET name = EXCLUDED.name;

I tried changing the excluded.fav_num and excluded.name to values but I still encounter the same error. I'm running PostgreSQL 10.6. What should be the proper query?

Table:

|------|------|---------|
|  id  | name | fav_num |
|------|------|---------|
|  1   | bob  |    1    |
|------|------|---------|

tadman · Accepted Answer · 2019-01-18 18:00:31Z

3

It's SET as in:

SET fav_num = EXCLUDED.fav_num, name = EXCLUDED.name

Note one and only one SET.

answered Jan 18, 2019 at 18:00

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Garrett Motzner Over a year ago

From the link below: The action can be: DO NOTHING – means do nothing if the row already exists in the table. DO UPDATE SET column_1 = value_1, .. WHERE condition – update some fields in the table.

tadman Over a year ago

It's the same as UPDATE.

Sean Ervinson Over a year ago

Ohh my bad cause most of the example in the internet only has one column so I figured the "..." was another set. It works thank you

Collectives™ on Stack Overflow

How to fix syntax error in postgresql UPSERT?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related