Skip to content
Advertisement

How to enforce that there is only one `true` value in a column per names (in an enum) of another column?

I have the following structure with an enum { 'ready', 'set', 'go'} on name:

|----------------------|---------------------------|------------------------|
|          Id          |          enabled          |          name          |
|----------------------|---------------------------|------------------------|
|          1           |          true             |        'ready'         |
|----------------------|---------------------------|------------------------|
|          2           |          false            |        'ready'         |
|----------------------|---------------------------|------------------------|
|          3           |          false            |        'ready'         |
|----------------------|---------------------------|------------------------|
|          4           |          false            |        'set'           |
|----------------------|---------------------------|------------------------|
|          5           |          true             |        'set'           |
|----------------------|---------------------------|------------------------|
|          6           |          true             |        'go'            |
|----------------------|---------------------------|------------------------|
|          7           |          false            |        'go'            |
|----------------------|---------------------------|------------------------|

How can I put a constraint on it so that there will only ever be 3 true‘s (one on ready, one on set, and one on go)?

Advertisement

Answer

You can use filtered unique indexes (or as Postgres calls them “partial indexes”):

create unique index unq_t_name
    on t(name)
    where enabled;
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement