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