Avoid inserting duplicates when using autoincrementing index

Question

I have a query:

INSERT INTO tweet_hashtags(hashtag_id, tweet_id)
VALUES(1, 1) 
ON CONFLICT DO NOTHING 
RETURNING id

which work fine and inserts with id = 1, but when there is a duplicate let's say another (1, 1) it inserts with an id = 2. I want to prevent this from happening and I read that I can do ON CONFLICT (col_name) but that doesn't really help because I need to check for two values at a time.

Answer 1

The on conflict clause requires a unique constraint or index on the set of columns that you want to be unique - and it looks like you don't have that in place.

You can set it when you create table table:

create table tweet_hashtags(
    id serial primary key, 
    hashtag_id int, 
    tweet_id int, 
    unique (hashtag_id, tweet_id)
);

Or, if the table already exists, you can create a unique index (but you need to get rid of the duplicates first):

create unique index idx_tweet_hashtags on tweet_hashtags(hashtag_id, tweet_id);

Then your query should just work:

insert into tweet_hashtags(hashtag_id, tweet_id)
values(1, 1) 
on conflict (hashtag_id, tweet_id) do nothing 
returning id

Specifying the conflict target makes the intent clearer and should be generally preferred (although it is not mandatory with do nothing).

Note that the query returns nothing when the insert is skipped (that is, the existing id is not returned).

Here is a demo on DB Fiddle that demonstrates the behavior with and without the unique index.