Re: enforcing alphabetic/numeric content in "char" column

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Joel Rodrigues
> Sent: Friday, November 08, 2002 7:11 AM
> To: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] enforcing alphabetic/numeric content in "char"
> column
>
>
> On Thursday, November 7, 2002, at 10:31 , Bruno Wolff III wrote:
>
> > On Thu, Nov 07, 2002 at 19:37:23 +0530,
> >   Joel Rodrigues <borgempath@Phreaker.net> wrote:
> >>
> >> iso3dnum    char(3) CHECK (iso3dnum ~ '[0-9]')
> >
> >   iso3dnum    char(3) CHECK (iso3dnum ~ '^[0-9][0-9][0-9]$')
> >
>
> Thank you Bruno & Rod ! This one works. So it's one [0-9] for
> every character.
>
> Obrigado !
> Joel
>

My original, as was pointed out, missed something important.  I had written
'^[0-9]$' which would validate one character.  Bruno's suggestion will work
as will '^[0-9]{3}$'.  The important thing to pick up on is the leading '^'
and trailing '$' which indicate the start and end of the string
respectively.  Without them, any other character can exist in the string IN
ADDITION TO the pattern specified.

IOW, '^[0-9]{3}$'  literally means a 3 digit string and nothing else (i.e.
000 - 999).  The pattern '[0-9]{3}'  means any string that contains a 3
digit sequence.  e.g. 'ab345kdjflkasdjf' would be valid.

HTH