Add constraint to make column unique per group of rows

Question

There is a column status in a Postgres table which can take only two values: Active and Inactive.

One of the columns is named userid. The table can have multiple rows with the same userid but at most one of them can have status = 'Active'. I need only one or no status as Active per userid. How can I create a constraint with this condition? I couldn't find any help from the Postgres documentation.

Plainsong

Better add column IsActive of boolean type instead of storing VARCHAR or if you have more statuses use INT type and add lookup table. – Sirloin

Thats a better alternative but I can't change it as whole database structure is following this. – Plainsong

So, there can be more than on records in this table with the same userid ? Than what is the meaning/intention of this table, some kind of status log? – Bungalow

@Bungalow its analogus to chat status. One user can save multiple status in database but can use only one at a time. – Plainsong

I don't know what "chat status" is. You could add some sample data to your question to show what you have and what you want. – Bungalow

Answer 1

status should really be boolean. Cheaper, cleaner.

Either way, you can impose your rule with a partial unique index.

To allow zero or one row with status = 'Active' in the whole table:

CREATE UNIQUE INDEX tbl_active_uni ON tbl (status)
WHERE status = 'Active';

To allow zero or one row with status = 'Active' per userid, make userid the indexed column:

CREATE UNIQUE INDEX tbl_userid_active_uni ON tbl (userid)
WHERE status = 'Active';

Null values in userid do not trigger unique violations, because two null values are not considered equal - at all, or by default since Postgres 15 which added the NULLS NOT DISTINCT clause. If you don't want that, set the column NOT NULL, or see:

• Create unique constraint with null columns
• How to add a conditional unique index on PostgreSQL

Why use an index and not a constraint?

Addressing your question in the comment: This is an index, not a CONSTRAINT.

The index for the first case is tiny, holding one or no row.
The index for the second case holds one row per existing userid, but it's the cheapest and fastest way, in addition to being clean and safe. You would need an index to check on other rows in any case to make this fast.

You cannot have a CHECK constraint check on other rows - at least not in a clean, reliable fashion. There are ways I would certainly not recommend for this case:

• Trigger vs. check constraint
• How to avoid a cyclic dependency (circular reference) between 3 tables?
• Disable all constraints and table checks while restoring a dump

If you use a UNIQUE constraint on (userid, status) (which is also implemented with a unique index internally!), you cannot make it partial, and all combinations are enforced to be unique. You could still use this if you work with status IS NULL for all cases except the 'Active' case. But that would create a much bigger index including all rows.

Loge

Though the solution works but isn't it an index instead of constraint. I mean creating index use extra memory to save column data in ordered fashion. Is it fine to use indexes in place of constraints? – Plainsong

@ShashwatKumar: See added explanation above. – Loge

The solution seems to be not working. I am adding two rows with same user id and status Inactive but on second insertion it is giving duplicate key error. When I am checking table information using \d is showing showing... "tb1_userid_key" UNIQUE CONSTRAINT, btree (userid) . With this conditon its obvious that same userid wont be accepted in the table. – Plainsong

@ShashwatKumar: With a unique constraint all combinations are enforced to be unique. I am suggesting the partial unique index instead. Please read again. – Loge

I used the same command that you gave. The unique constraint was result ot that. – Plainsong

@ShashwatKumar. CREATE UNIQUE INDEX ... does not create a CONSTRAINT. tb1_userid_key must have been created some other way. – Loge

This works when there are only two different possible values for status. But what if there's an indefinite number of groups? For example, say you have a tree-like structure and want to make sure a text identifier is unique among all the identifiers that have the same parent. – Robbyrobbyn

@flodin: Good question. I encourage you to ask it as question. Comments are not the place. You can always link to this one for context and/or drop a comment here to link back. – Loge

@ErwinBrandstetter well if I ask it as a question it'll most likely get marked as a duplicate of this question. ;) But I'll try. – Robbyrobbyn