Re: How to add month.year column validation

On Wed, Dec 23, 2009 at 12:02 PM, Andy Shellam
<andy-lists(at)networkmail(dot)eu> wrote:
> 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.

You can use the regex I posted to get rid of the data easily, then go
back to the substr one for a check constraint after that.