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
