Thread: Weirdness in CHECK?

I just ran across this recent example:

If you perform the following, you get a truncated input:

test=# create table example (type char(5) NOT NULL);
CREATE
test=# insert into example VALUES ('VOLUME');
INSERT 156884 1
test=# select * from example;type
-------VOLUM
(1 row)

However, if you add CHECK in that checks for a string that is LONGER than
the CHAR(5), you get this:

test=# create table example(type char(5) NOT NULL CHECK (type IN
('MASS','VOLUME')));
CREATE
test=# insert into example VALUES ('VOLUME');
ERROR:  ExecAppend: rejected due to CHECK constraint example_type

Is this correct behaviour?  Perhaps it is, as CHECK is checking the
truncated value - but I just want to make sure it's not a bug!

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> Is this correct behaviour?  Perhaps it is, as CHECK is checking the
> truncated value - but I just want to make sure it's not a bug!

I think some people feel that we ought to raise an error rather than
silently truncating the input.  However, given that we do intend to
truncate the input, it seems to me that applying CHECK constraints
post-truncation is the Right Thing.  In general a CHECK ought to be
applied after any conversion ops needed to create a value of the
destination column type.  For example, if I had "CHECK (foo < 1)"
on a column foo declared NUMERIC(3,2), I'd be unhappy if the input
"0.999" got past the check because it was rounded to 1.00 only after
the CHECK was applied.
        regards, tom lane

> I think some people feel that we ought to raise an error rather than
> silently truncating the input.  However, given that we do intend to
> truncate the input, it seems to me that applying CHECK constraints
> post-truncation is the Right Thing.  In general a CHECK ought to be
> applied after any conversion ops needed to create a value of the
> destination column type.  For example, if I had "CHECK (foo < 1)"
> on a column foo declared NUMERIC(3,2), I'd be unhappy if the input
> "0.999" got past the check because it was rounded to 1.00 only after
> the CHECK was applied.

Agreed.  However, my complaint is with the error message.  I spent half an
hour dumping and recreating my tables, messing with the catalogs, etc.
because I could not for the life of me figure out what was wrong with my
CHECKs!  Why wouldn't it accept 'VOLUME' when 'VOLUME' was clearly being
passed by the CHECK!  It drove me nuts until I noticed that I'd accidentally
set the CHAR length too low.

Chris