On Saturday 28 February 2004 08:36, Rajat Katyal wrote: > Ya, through application we can provide field input validation but iam > working on the tool which transfers the data from some specifed database to > another. Here user itself wants if value is too long for the target column > then truncates the text and insert into the target field. It was there in > postgres version 7.2 but not in postgres 7.3. > > At database level how can we provide a check if text size is greater than > that of field size then truncate it?
Unfortunately, you can't do this with a BEFORE INSERT trigger, since the type-checking (which includes length) is done before the trigger will get called. You could however: 1. Have a duplicate table, except with unlimited varchar fields and import into that. Once the batch is in, move it to the destination table with the relevant substr() 2. You *should* be able to do the same, but with a before trigger that trims, then inserts to the destination table before returning NULL. 3. You might even be able to use a view with a rule that instead trims the relevant text fields. Not sure about this one. -- Richard Huxton Archonet Ltd ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
