0

I have a database table in PostgreSQL called "item" that have 3 columns

id: UUID
name: VARCHAR
type: VARCHAR

The "id" column is a primary key and there is no other constraint or index in this table.

My requirements are: if two rows have the same name, they should not have the same type which means the same type can not be attached to the same name more than once.

Here is my question:
How to insert a new row with maintaining my previous condition and taking into consideration the race condition that night happen with concurrent transactions?

I have already read about conditional insert (using WHERE NOT EXIST) but it has a caveat as mentioned here: https://stackoverflow.com/a/13342031/7972923

VFX
  • 496
  • 4
  • 13

1 Answers1

1

put unique index on (name,type)

CREATE UNIQUE INDEX your_idx ON items (name, type);

This will ensure that there are no two duplicates on (name, type) tuple which I believe achieves if two rows have the same name, they should not have the same type

dfens
  • 5,413
  • 4
  • 35
  • 50
  • thanks for the answer, I can also use a unique constraint on multiple columns, right? – VFX Nov 03 '20 at 14:24
  • 1
    Exactly, https://www.postgresqltutorial.com/postgresql-indexes/postgresql-unique-index/ see multiple columns example – dfens Nov 03 '20 at 14:29