Re: How to add month.year column validation

CREATE DOMAIN Nasty_Month_year AS CHAR(7)
CHECK ( SUBSTR(VALUE, 1, 2) IN ('01', '02', '03', '04', '05', '06',
'07', '08', '09', '10', '11', '12') AND SUBSTR(VALUE, 3, 1) = '.' AND
SUBSTR(VALUE, 4, 4)::int BETWEEN 1980 and 2110 );

CREATE TABLE foo (bar Nasty_Month_year );

This Works:
INSERT INTO foo VALUES ('02.1997');

This:
INSERT INTO foo VALUES ('13.1997');
Gives this:
ERROR: value for domain nasty_month_year violates check constraint
"nasty_month_year_check"

********** Error **********

ERROR: value for domain nasty_month_year violates check constraint
"nasty_month_year_check"
SQL state: 23514

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Andrus
> Sent: Tuesday, December 22, 2009 12:47 PM
> To: Michael Glaesemann
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 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.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general