Thread: Newbie question

On Thu, 26 Jun 2003, Vinay wrote:

> I have a very simple question regarding creating columns in a table
>
> Can we create a column in a table in such a way that it will allow only certain length, nothing less or nothing more
>
> here is an example
>
>
> create table test(column1 char(2));
>
> will create table with column1 which will accept characters upto length two.
> I want the column to accept the column value which is absolutely length of two.

create table p (name text check (length(name)=2));

I'm not sure I can make it work for char(), it seems, since char is always
autopadded out to two characters before it gets to the check constraint.

It will work with text and varchar(2) types.

On Thu, Jun 26, 2003 at 15:59:55 -0400,
  Vinay <vinay@mdp.net> wrote:
> I have a very simple question regarding creating columns in a table
>
> Can we create a column in a table in such a way that it will allow only certain length, nothing less or nothing more
>
> here is an example
>
>
> create table test(column1 char(2));
>
> will create table with column1 which will accept characters upto length two.
> 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]$'
);

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