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


Thu, 4 Jan 2001 13:06:36 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Weirdness in CHECK?
"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

Thu, 04 Jan 2001 00:27:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Weirdness in CHECK? 