Insert into PostgreSQL table if a unique column combination doesn't exist, and if it does, update the existing record

Question

I have a table structured with columns user_id, item_id, test

I'd like to make an INSERT/UPDATE query so that if the passed in user_id and item_id combination is already found in a row of the table, it just updates the test of that row. I've tried the following query but it doesn't work:

INSERT INTO tableName (user_id, item_id, test) 
VALUES($1, $2, $3) 
ON CONFLICT ON CONSTRAINT UNIQUE(user_id, item_id) 
DO UPDATE SET test = ($3)

This doesn't work. I've tried playing around with DISTINCT keyword as well but didn't manage to get it working correctly. Any help much appreciated!

klin · Accepted Answer · 2021-11-28 05:28:50Z

4

You need a unique index on both columns. It can be a composite primary key or a composite unique constraint, example:

create table tablename(
    user_id int, 
    item_id int, 
    test text,
    primary key (user_id, item_id)
);

Then use the simple proper syntax:

insert into tablename (user_id, item_id, test) 
values(1, 1, '1') 
on conflict (user_id, item_id) 
do update set test = excluded.test

answered Nov 28, 2021 at 5:28

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Insert into PostgreSQL table if a unique column combination doesn't exist, and if it does, update the existing record

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