Re: How to add month.year column validation

On Dec 22, 2009, at 15:03 , Andrus wrote:

> Database contains CHAR(7) type column which must be NOT NULL and
> must be in the format
>
> mm.yyyy
>
> where:
>
> mm - month number, always two digits in range 01 .. 12
>
> . - separator  must be point always.
>
> yyyy - must be four digits in range approx. 1980 .. 2110 .
>
> How to add column validation to table column which forces this ?

A check constraint with regex might work, something like (untested)

CHECK (val ~ $re$^(19|20|21)[0-9]{2}.[01][0-9]$$re$)

However, I strongly recommend using a date column with, perhaps, a
restriction that the day field is always 1 or some other agreed-upon
(and documented) value (e.g., CHECK (val = date_truc('month', val))).
If the data is date data, you're likely going to want to do other
operations on the field which will be much easier if it's already a
date value.

Michael Glaesemann
grzm seespotcode net