Re: BUG #8141: multi-column check expression evaluating to NULL - Mailing list pgsql-bugs

On 09/05/13 01:07, andras.vaczi@zalando.de wrote:
> The following bug has been logged on the website:
>
> Bug reference:      8141
> Logged by:          Andras Vaczi
> Email address:      andras.vaczi@zalando.de
> PostgreSQL version: 9.1.9
> Operating system:   linux/Ubuntu 12.10
> Description:
>
> Consider the following table with a CHECK constraint:
>
> CREATE TABLE check_test
> (
>    id integer NOT NULL,
>    col integer,
>    CONSTRAINT unique_with_null_check1 CHECK (col >= 1 AND id < 20)
> );
>
[...]

if 'id' is the primary key, then the table should be defined as:

CREATE TABLE check_test
(
   id  integer PRIMARY KEY,
   col integer NOT NULL,
   CONSTRAINT unique_with_null_check1 CHECK (col >= 1 AND id < 20)
);

Because:

 1.   'PRIMARY KEY ' implies 'NOT NULL' and also 'UNIQUE', and so an
    index is created for 'id'
    (index is required to enforce uniqueness)

 2. you are testing that 'col' has a certain type of numeric value, so
    this logically implies that it is 'NON NULL', but you actually have
    to tell progress explicitly tat you want it to be 'NON NULL'



Cheers,
Gavin