Re: Can I use check-constraint to set null? - Mailing list pgsql-novice

Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> writes:
> CREATE TABLE test (foo numeric(8,2) CHECK (foo >= 0.0));

> ecdb=> insert into test values (-2.2);
> ERROR:  new row for relation "test" violates check constraint "test_foo_check"

> This is expected.

> However, is there a way to insert a null when this check fails,
> instead of returning an error (and thus aborting the insert)?

You could write a BEFORE INSERT trigger that does something like

    IF NOT NEW.foo >= 0.0 THEN
        NEW.foo := NULL;
    END IF;

            regards, tom lane