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