Re: How to add month.year column validation
От | Andrus |
---|---|
Тема | Re: How to add month.year column validation |
Дата | |
Msg-id | 668ED36A59164EB7B75D231D04F81288@andrusnotebook обсуждение исходный текст |
Ответ на | Re: How to add month.year column validation (Michael Glaesemann <grzm@seespotcode.net>) |
Ответы |
Re: How to add month.year column validation
Re: How to add month.year column validation |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: