PostgreSQL Upsert Error when specify the table name

Question

im tryign to upsert on PostgreSQL using this query

insert into users (id,name) values (1,'henry')
on conflict (users.id) do update set
(id,name)=(EXCLUDED.id,EXCLUDED.name)

and keep getting this error

ERROR:  syntax error at or near ")"
LINE 2: on conflict (users.id) do update set

it works if i do it this way

insert into users (id,name) values (1,'henry')
on conflict (id) do update set
(id,name)=(EXCLUDED.id,EXCLUDED.name)

how to specify the table name on the query? like users.id

What version of PostgreSQL are you using? You need version 9.5 or later. — Dai, Jun 23 '20 at 03:24
Does this answer your question? [How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?](https://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgresql) — Dai, Jun 23 '20 at 03:26

score 0 · Answer 1 · answered Jun 23 '20 at 03:31

You cannot qualify the column name with the table name in the ON CONFLICT clause; the syntax forbids it:

where conflict_target can be one of:

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]

ON CONSTRAINT constraint_name

You are using the index_column_name syntax, so it must look just like in CREATE INDEX, where the table name is also not allowed.

There is no need to add the table name, because the can be only one table.

PostgreSQL Upsert Error when specify the table name

1 Answers1