Postgres 9.2 - add conditional constraint check

Question

I'm using PostgreSQL 9.2 and need to add a conditional constraint on a column. Essentially I want to make sure that a column is false when two other columns have a certain value.

gid          | int_unsigned           | not null default 0
realm        | character varying(255) | not null default ''::character varying
grant_update | smallint_unsigned      | not null default (0)::smallint
grant_delete | smallint_unsigned      | not null default (0)::smallint

Example:

alter table node_access add constraint block_anonymous_page_edit
check (grant_update = 0 WHERE (gid = 1 AND realm = 'nodeaccess_rid'));

What this is supposed to do is make sure that grant_update is equal to 0 when gid is 1 and realm = nodeaccess_rid. However, I think rather than doing what I want, it's actually trying to make all columns mimic these values. In essence, it's trying to make sure that grant_update is always 0, gid is always 1, and realm is always nodeaccess_rid. The error I get is:

ERROR:  check constraint "block_anonymous_page_edit" is violated by some row

EDIT

I think this is going to have to be a function that gets triggered on update.

EDIT

I added a row to the question above, and consequently updated the approved solution with a comment below.

Answer 1

Once you wrap your mind around the logic, it's simple CHECK constraint:

CREATE TABLE tbl (
  gid          int      NOT NULL DEFAULT 0
, realm        text     NOT NULL DEFAULT ''
, grant_update smallint NOT NULL DEFAULT 0
, CONSTRAINT block_anonymous_page_edit
  CHECK (gid <> 1 OR realm <> 'nodeaccess_rid' OR grant_update = 0)
);

Test:

-- these work:
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 0);

INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'some_string',    1);

INSERT INTO tbl(gid, realm, grant_update)
VALUES (2, 'nodeaccess_rid', 1);

-- check violation!
INSERT INTO tbl(gid, realm, grant_update)
VALUES (1, 'nodeaccess_rid', 1);

db<>fiddle here

Answer 2

I would write this as a trigger. This gives you the flexibility of either raising an error (potentially with a custom code that can best tested for) or just handling the problem and setting grant_update = 0 when gid=1 and realm = 'nodeaccess_rid'