PostgreSQL Insert Random Values Into Table And Avoid Duplication For Rows With The Same Type

Question

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

dfens · Accepted Answer · 2020-11-03 14:13:11Z

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

edited Nov 3, 2020 at 14:13

answered Nov 3, 2020 at 14:07

dfens

5,5634 gold badges37 silver badges52 bronze badges

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

2 Comments

VFX Over a year ago

thanks for the answer, I can also use a unique constraint on multiple columns, right?

dfens Over a year ago

Exactly, postgresqltutorial.com/postgresql-indexes/… see multiple columns example

Collectives™ on Stack Overflow

PostgreSQL Insert Random Values Into Table And Avoid Duplication For Rows With The Same Type

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related