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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
