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?

Jordi Cabot · Accepted Answer · 2009-07-26 13:32:15Z

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

ollyc · Accepted Answer · 2009-07-25 12:29:22Z

3

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)

edited Jul 25, 2009 at 12:29

answered Jul 25, 2009 at 10:50

ollyc

6,2011 gold badge19 silver badges8 bronze badges

2 Comments

Daniel Rikowski Over a year ago

This is simply not necessary. Quote from the PostgreSQL manual: It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. (postgresql.org/docs/8.1/static/ddl-constraints.html)

ᴇʟᴇvᴀтᴇ Over a year ago

It may not be necessary, but that doesn't mean it's not better to be explicit about the logic.

Collectives™ on Stack Overflow

postgres: constraint check and null values

2 Answers 2

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Related