Using multiple unique constraints in one postgresql table

Question

I am trying to write a command which adds a row to my postgreSQL table if two of the columns do not contain the same data else where. Neither user_id nor venue_id need to be unique however I do not want to add a new row that has the same user_id AND venue_id as a previous row.

My table

id user_id venue_id like dislike
1   1        1       1    0
2   1        2       0    0
3   2        2       1    1

Currently I am trying

INSERT INTO user_matches (user_id, venue_id) VALUES (1,1) ON CONFLICT DO NOTHING;

Gordon Linoff · Accepted Answer · 2020-10-21 02:22:14Z

0

You simply want a unique index/constraint on the two columns:

alter table user_matches add constraint unq_user_matches_user_venue
    unique (user_id, venue_id);

You can then use this constraint for on conflict.

answered Oct 21, 2020 at 2:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

user11631308 Over a year ago

Lindoff, it seems like this will work for single entries however I using a multiple input statement INSERT INTO user_matches (user_id, venue_id) VALUES (1, 10), (1,20), (1,3); and I do not want my entire query to fail when just one of the entries is a duplicate.

Gordon Linoff Over a year ago

@tdammon . . . That is why you use on conflict ignore.

Collectives™ on Stack Overflow

Using multiple unique constraints in one postgresql table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related