Re: How to add month.year column validation
От | Andrus |
---|---|
Тема | Re: How to add month.year column validation |
Дата | |
Msg-id | AEC49848C999465483CD00324252CF5F@andrusnotebook обсуждение исходный текст |
Ответ на | Re: How to add month.year column validation ("Dann Corbit" <DCorbit@connx.com>) |
Ответы |
Re: How to add month.year column validation
|
Список | pgsql-general |
Dann, >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 ); Thank you. This looks better than regexp since it conforms to SQL standard. regexps are PostgreSql specific. I created test script to test changing existing database column with possibly wrong data: CREATE DOMAIN MonthYear AS CHAR(7) NOT NULL 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 2100 ); CREATE TABLE foo (tmkuu char(7)); -- add bad data INSERT INTO foo VALUES (''); INSERT INTO foo VALUES ('02.x'); INSERT INTO foo VALUES ('02.1970'); INSERT INTO foo VALUES ('02.2101'); INSERT INTO foo VALUES (NULL); -- add good data INSERT INTO foo VALUES ('12.2009'); delete from foo where tmkuu is null OR NOT ( SUBSTR(tmkuu, 1, 2) IN ('01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12') AND SUBSTR(tmkuu, 3, 1) = '.' AND SUBSTR(tmkuu, 4, 4)::int BETWEEN 1980 and 2100 ); alter table foo alter tmkuu type MonthYear; select * from foo; but got error on DELETE: ERROR: invalid input syntax for integer: "x" How to apply this constraint to existing data ? Andrus.
В списке pgsql-general по дате отправления: