postgres: constraint check and null values

Question

please have a look at the following table:

   name  |  x  |  y
---------+-----+------
   foo   |  3  |  5
   bar   | 45  | 99 
  foobar | 88  | 
  barfoo |  0  | 45

I want to add a constraint CHECK ( y > x ), but this obviously will fail due it is violated by the row 'foobar'.

How do I create a constraint that says: check (y > x), but only if y not null?

Answer 1

In fact, you don't really need to do anything else. A check constraint is satisfied if the check expression evaluates to true or the null value.

Your constraint CHECK ( y > x ) would work as is in your scenario because the row foobar would not violate the constraint since it evaluates to null

Answer 2

You can put an IS NULL test into the CHECK expression, like this:

CREATE TABLE mytable (
    name TEXT,
    x INTEGER,
    y INTEGER CHECK (y IS NULL OR y > x)
);

(tested on PostgreSQL 8.4)