15

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.

Shashwat Kumar
  • 5,159
  • 2
  • 30
  • 66
  • 2
    Better add column `IsActive` of boolean type instead of storing `VARCHAR` or if you have more statuses use INT type and add lookup table. – Lukasz Szozda Dec 28 '15 at 14:38
  • Thats a better alternative but I can't change it as whole database structure is following this. – Shashwat Kumar Dec 28 '15 at 14:40
  • 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? – joop Dec 28 '15 at 14:56
  • @joop its analogus to chat status. One user can save multiple status in database but can use only one at a time. – Shashwat Kumar Dec 28 '15 at 15:01
  • 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. – joop Dec 28 '15 at 15:11

1 Answers1

21

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:

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:

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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? – Shashwat Kumar Dec 28 '15 at 15:06
  • @ShashwatKumar: See added explanation above. – Erwin Brandstetter Dec 28 '15 at 15:17
  • 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. – Shashwat Kumar Dec 29 '15 at 12:39
  • @ShashwatKumar: With a unique ***constraint*** `all combinations are enforced to be unique`. I am suggesting the ***partial unique index*** instead. Please read again. – Erwin Brandstetter Dec 29 '15 at 14:02
  • I used the same command that you gave. The unique constraint was result ot that. – Shashwat Kumar Dec 29 '15 at 20:01
  • @ShashwatKumar. `CREATE UNIQUE INDEX ... ` does *not* create a `CONSTRAINT`. `tb1_userid_key` must have been created some other way. – Erwin Brandstetter Dec 29 '15 at 20:13
  • 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. – flodin Feb 14 '22 at 14:33
  • 1
    @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. – Erwin Brandstetter Feb 14 '22 at 19:03
  • @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. – flodin Feb 15 '22 at 09:37