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!

Answer 1

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