Postgres constraint for a subset of rows

Question

I have a Postgres table containing tasks (tasks). A task can link to many entities using a link table (links). Tasks can be one of many types.

A subset of tasks, denoted by their types (let's call it S) can only link to one entity. That is, in link, there can be exactly one record with that task's ID/primary key.

Is there a way to encode that into Postgres constraints so that's managed automatically?

Answer 1

I found a solution to this using triggers:

CREATE OR REPLACE FUNCTION unique_link() RETURNS trigger AS $unique_link$
    DECLARE
        t_type text;
        link_ct int;
    BEGIN
        SELECT task_type INTO STRICT t_type FROM tasks WHERE id = NEW.task_id;
        IF t_type = 'S' THEN
            SELECT COUNT(*) INTO STRICT link_ct FROM links
                WHERE task_id = NEW.task_id
            IF link_ct > 0 THEN
                 RAISE EXCEPTION '% of type % already has a link associated', NEW.task_id, t_type;
            END IF;
            RETURN NEW;
        END IF;
        RETURN NEW;
    END;
$unique_link$ LANGUAGE plpgsql;

CREATE TRIGGER unique_link BEFORE INSERT OR UPDATE ON links
    FOR EACH ROW EXECUTE PROCEDURE unique_link();