Bruno Wolff III <bruno@wolff.to> writes:
>> I want the column to accept the column value which is absolutely length of two.
> A char(2) will always have two characters (unless it is NULL). If you want
> to check for two nonblank characters or two letters or something like
> that use a check constraint.
> Some like:
> create table test (
> col1 char(2) constraint bad_length check (col1 ~ '^[a-zA-Z][a-zA-Z]$'
> );
The most obvious way to my mind is
check(length(rtrim(col1)) = 2)
if "length" is simply defined as "number of characters excluding
trailing blanks". If you want a more complex check then something
like Bruno's example will probably get the job done.
If you are going to apply a constraint like this then I'd counsel just
declaring the column as text --- making it char(2) simply means that
the system is applying an extra check that is redundant with your
constraint.
regards, tom lane