Thread: check contraint allows illegal value?

Is this a bug?

create table foo( c char(2)
    check (c in ('09', '10', '11', '12', 'n/a'))
);
CREATE

insert into foo values('09');
INSERT

insert into foo values('10');
INSERT

insert into foo values('08');
ExecAppend: rejected due to CHECK constraint foo_c

insert into foo values('n/a');
ExecAppend: rejected due to CHECK constraint foo_c



clearly this was a 'whups' on my part, but it would have
been nice to know that my constraint was bogus at the
time the table was created.

>
> Is this a bug?
>
> create table foo( c char(2)
>     check (c in ('09', '10', '11', '12', 'n/a'))
> );
> CREATE
>
> insert into foo values('09');
> INSERT
>
> insert into foo values('10');
> INSERT
>
> insert into foo values('08');
> ExecAppend: rejected due to CHECK constraint foo_c
>
> insert into foo values('n/a');
> ExecAppend: rejected due to CHECK constraint foo_c
>
>
>
> clearly this was a 'whups' on my part, but it would have
> been nice to know that my constraint was bogus at the
> time the table was created.

That is interesting.  You want the check constraint to be a valid value
for the column.  I can see char() having this issue.  Is this something
we should check for all types folks?  Not sure.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

missive@frontiernet.net (Lee Harr) writes:
> Is this a bug?

> create table foo( c char(2)
>     check (c in ('09', '10', '11', '12', 'n/a'))
> );

I don't think so; at least, I don't think there's any way in the general
case for the system to realize that some part of a check condition can
never succeed or never fail.  (See "halting problem".)

            regards, tom lane