wierd case of 'unique' constraint (possible case for exclusion) | Postgres

user_11251373 0
Viewed 40

I have a question regarding constraint(unique or exclusion in this case I guess). I cant get my head around this simple case:

For example I have this simplified version of a table:

create table user
(
    id                            serial                   not null
    username                      varchar(30)              not null
        constraint user_username_key
            unique,
    is_active                     boolean                  not null,
    company_id                    integer                  not null
       constraint company_user_company_company_company_id
    references company_company
    deferrable initially deferred

I would like to create such constraint to satisfy following conditions:

If we have at least one (or possible few records) referencing same company_id (lets say = 3) that we must(it should exists) have one and only one username that ends on ‘-support’ (lets say ‘user1-support’) with is_active = True.

Wrong scenarios:

• we have one or more records but there are no  any username that ends on `‘-support’` amoung this records
• we have one or more records and we have multiple  usernames that ends on `‘-support’` amoung this records

If we have only one entry – then username should be ended with ‘-support’

Thanks

Sorry if question is naive

postgres 11 version

postgresql-11 postgresql sql
1 Answers

Filtered unique index could be used:

CREATE UNIQUE INDEX idx ON "user"
  (company_id,(CASE WHEN username LIKE '%-support' THEN 'support' ELSE username END)) 
WHERE is_Active = True;

db<>fiddle demo

user_50708790
Related