Re: How to add month.year column validation

Andrus,

>
> -- 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 ?

Remove the data that doesn't conform to the constraint.

You got the error because you're trying to cast SUBSTR(tmkuu, 4, 4) to an integer in your DELETE statement - but in the case of the second record, that expression cannot be an integer (because of the x) hence the error.

Regards,
Andy