Unique constraint across two rows in PostgreSQL

I recently had a requirement where I needed an account to have zero, one or two actions associated with it. One could be a single action and the other could be one of many repeating types. I didn’t want two single actions and I didn’t want two or more types of repeating actions. To solve this I used two partial indexes to split the data set and apply a unique constraint to each set.

CREATE TABLE accounts (
  id   integer NOT NULL,
  name text    NOT NULL
);

CREATE TABLE actions (
  id          integer NOT NULL,
  account_id  integer NOT NULL,
  repeat_type text    NOT NULL DEFAULT 'none'
);

INSERT INTO accounts (id, name) VALUES (1, 'Test 1'), (2, 'Test 2');

If I create a unique index on actions(account_id) then I will only be able to have a single action per account.

CREATE UNIQUE INDEX idx_unique_accounts ON actions(account_id);

INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- INSERT 0 1
INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'weekly');
-- ERROR:  duplicate key value violates unique constraint "idx_unique_accounts"
-- DETAIL:  Key (account_id)=(1) already exists.

DROP INDEX idx_unique_accounts;

The solution is to create two partial indexes, one for the single action and one for the repeating action.

TRUNCATE TABLE actions;
CREATE UNIQUE INDEX idx_unique_single_actions    ON actions(account_id) WHERE (repeat_type = 'none');
CREATE UNIQUE INDEX idx_unique_repeating_actions ON actions(account_id) WHERE (repeat_type != 'none');

INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- INSERT 0 1
INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'weekly');
-- INSERT 0 1

Now inserting another single action will result in an error.

INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'none');
-- ERROR:  duplicate key value violates unique constraint "idx_unique_single_actions"
-- DETAIL:  Key (account_id)=(1) already exists.

Or inserting another repeating action, even of a different repeat type, will result in an error.

(sql) INSERT INTO actions (id, account_id, repeat_type) VALUES (1, 1, 'monthly'); -- ERROR: duplicate key value violates unique constraint "idx_unique_repeating_actions" -- DETAIL: Key (account_id)=(1) already exists.