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 a rather simple CHECK constraint:

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

Test:

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

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

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

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