Re: How to add month.year column validation

On Dec 22, 2009, at 16:11 , Scott Marlowe wrote:

> alter table test add constraint test_a_check check (a ~
> $r$^(0[1-9]|1[0-2]).(19[89][0-9]|20[0-9]{2}|210[0-9]|2110)$$r$);
>
>>> 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.
>>
>> This is existing database and many application are using it.
>> I cannot change column type to date since other applications are
>> expecting
>> char(7) column.
>
> Just so you know, down this road lies madness.

I completely agree. Schedule some downtime and make the column a date
column.

Michael Glaesemann
grzm seespotcode net