Postgresql Upsert - syntax error on DO UPDATE

Question

I'm trying to perform an Upsert but can't figure out what the correct syntax is. Could someone please help me identify the issue?

INSERT INTO owners (contract_address, owner_address, owns_sticker, owns_balloon)
VALUES(
    'cxd70e445647f50560d495d695b7becd3e88083222', 
    'hxfcba5ec13ba9791dcb52de1f8e25ae7625762222',
    1,
    1
) ON CONFLICT (contract_address)
DO UPDATE owners SET owns_sticker = 0, owns_balloon = 0 WHERE contract_address = 
    'cxd70e445647f50560d495d695b7becd3e88083222'

Syntax Error:

ERROR:  syntax error at or near "owners"
LINE 8: DO UPDATE owners SET owns_sticker = 0, owns_balloon = 0 WHERE ...

I'm following the postgresql tutorial from:

https://www.postgresqltutorial.com/postgresql-upsert/

I'm not quite sure how to translate their example to a working one for my situation:

You don't need to repeat the table name in the do update part. It should be DO UPDATE SET owns_sticker = ... — user330315
– user330315, Commented Jan 30, 2022 at 9:13

rajorshi · Accepted Answer · 2022-01-30 09:16:04Z

Here is the error reproduced:

postgres=# create table owners( contract_address varchar(100) primary key ,owner_address varchar(100),owns_sticker int, owns_balloon int);
CREATE TABLE
postgres=# INSERT INTO owners (contract_address, owner_address, owns_sticker, owns_balloon)
postgres-# VALUES(
postgres(#     'cxd70e445647f50560d495d695b7becd3e88083222',
postgres(#     'hxfcba5ec13ba9791dcb52de1f8e25ae7625762222',
postgres(#     1,
postgres(#     1
postgres(# );
INSERT 0 1
postgres=# select * from owners;
              contract_address              |               owner_address                | owns_sticker | owns_balloon
--------------------------------------------+--------------------------------------------+--------------+--------------
 cxd70e445647f50560d495d695b7becd3e88083222 | hxfcba5ec13ba9791dcb52de1f8e25ae7625762222 |            1 |            1
(1 row)

postgres=# INSERT INTO owners (contract_address, owner_address, owns_sticker, owns_balloon)
postgres-# VALUES(
postgres(#     'cxd70e445647f50560d495d695b7becd3e88083222',
postgres(#     'hxfcba5ec13ba9791dcb52de1f8e25ae7625762222',
postgres(#     1,
postgres(#     1
postgres(# ) ON CONFLICT (contract_address)
postgres-# DO UPDATE owners SET owns_sticker = 0, owns_balloon = 0 WHERE contract_address =
postgres-#     'cxd70e445647f50560d495d695b7becd3e88083222';
ERROR:  syntax error at or near "owners"
LINE 8: DO UPDATE owners SET owns_sticker = 0, owns_balloon = 0 WHER...
                  ^
postgres=# INSERT INTO owners (contract_address, owner_address, owns_sticker, owns_balloon)
VALUES(
    'cxd70e445647f50560d495d695b7becd3e88083222',
    'hxfcba5ec13ba9791dcb52de1f8e25ae7625762222',
    1,
    1
) ON CONFLICT (contract_address)
DO UPDATE owners SET owns_sticker = 0, owns_balloon = 0;
ERROR:  syntax error at or near "owners"
LINE 8: DO UPDATE owners SET owns_sticker = 0, owns_balloon = 0;

And here is the fix:

postgres=# INSERT INTO owners (contract_address, owner_address, owns_sticker, owns_balloon)
VALUES(
    'cxd70e445647f50560d495d695b7becd3e88083222',
    'hxfcba5ec13ba9791dcb52de1f8e25ae7625762222',
    1,
    1
) ON CONFLICT (contract_address)
DO UPDATE SET owns_sticker = 0, owns_balloon = 0;
INSERT 0 1
postgres=#

Collectives™ on Stack Overflow

Postgresql Upsert - syntax error on DO UPDATE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related