Re: How to add month.year column validation

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.