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.
