Postgres. Conditional stored data uniqueness

Question

I have an Application type in my app.

public class Application {
   private String id;
   private String creator;
   private boolean active;
}

active field marks is current application actual for certain user. Every user is able to have only one application in active(active == true) state and any number of inactive applications(active == false).

Is it possible to deny storing second application record with same creator value and active == true, but let save any number of applications with same creator value and active == false on DB level(Postgres)?

Thanks.

Bergi · Accepted Answer · 2021-04-30 10:27:04Z

2

Yes, a partial unique index can solve this:

CREATE UNIQUE INDEX active_application_per_user ON application (creator) WHERE active;

answered Apr 30, 2021 at 10:27

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Postgres. Conditional stored data uniqueness

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related