Re: value too long error

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