To: Bruno Wolff III <bruno@wolff.to>
Cc: Vinay <vinay@mdp.net>, pgsql-admin@postgresql.org
Subject: Re: Newbie question 
In-reply-to: <20030626203911.GA10461@wolff.to> 
References: <001c01c33c1d$843e0100$157a40c0@vinay>
	<20030626203911.GA10461@wolff.to>
Comments: In-reply-to Bruno Wolff III <bruno@wolff.to>
	message dated "Thu, 26 Jun 2003 15:39:11 -0500"
Date: Thu, 26 Jun 2003 22:47:42 -0400
Message-ID: <23574.1056682062@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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