Re: How to add month.year column validation

On Tue, Dec 22, 2009 at 1:47 PM, Andrus <kobruleht2(at)hot(dot)ee> wrote:
> 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"

Yeah, you've got some characters in odd places there (^ in particular,
$ missing at end.) Here's one that pretty much works:

create table test (a text check (a ~
$r$^(0[1-9]|1[0-2]).(19[89][0-9]|20[0-9]{2}|210[0-9]|2110)$$r$));

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

Make one that doesn't accept 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"

Left out a keyword, "constraint"

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. But you can make it
work. I've been down this path before too. Eventually your
developers doing dba work will paint you into a corner.