Re: How to add month.year column validation

Michael,

Thank you very much.
I have very few knowledge on rexexps.

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

1. I tried

create temp table test5 ( tmkuu char (7) CHECK (tmkuu ~
$re$[01][0-9].^(19|20)[0-9]{2}$re$) ) on commit drop;
insert into test5 values('01.2009');

but got error

ERROR: new row for relation "test5" violates check constraint
"test5_tmkuu_check"

2. How to restrict month numbers to range 01 .. 12 ?
This regex seems to accept month numbers like 13

3. How to add this check to existing column for 8.0 and later servers ?
I tried

alter table test5 alter tmkuu add CHECK (tmkuu ~
$re$[01][0-9].^(19|20)[0-9]{2}$re$ )

causes error:

ERROR: syntax error at or near "add"

> 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.

As far as I know it is not possible to makse such change so that externally
it is visible and writeable as char(7) column for 8.0+ servers.

Andrus.