Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can I use check-constraint to set null?

Can I use check-constraint to set null?

From
Rikard Bosnjakovic
Date:
(Postgres 8.4.2)

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)? I'm
reading on http://www.postgresql.org/docs/current/static/ddl-constraints.html
but cannot find an answer to this question.


--
- Rikard

Re: Can I use check-constraint to set null?

From
Tom Lane
Date:
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

Re: Can I use check-constraint to set null?

From
Andreas Kretschmer
Date:
Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> wrote:

> (Postgres 8.4.2)
>
> 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)? I'm
> reading on http://www.postgresql.org/docs/current/static/ddl-constraints.html
> but cannot find an answer to this question.

I think, you should use a TRIGGER.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Can I use check-constraint to set null?

From
Thom Brown
Date:
On 16 August 2010 19:34, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> wrote:
>
>> (Postgres 8.4.2)
>>
>> 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)? I'm
>> reading on http://www.postgresql.org/docs/current/static/ddl-constraints.html
>> but cannot find an answer to this question.
>
> I think, you should use a TRIGGER.
>

I don't think suicide's the answer.  Oh, wait, I see...
--
Thom Brown
Registered Linux user: #516935