From: | "Charles Clavadetscher" <clavadetscher(at)swisspug(dot)org> |
---|---|
To: | "'Kevin Golding'" <KGolding(at)AxessGroup(dot)com(dot)au>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: 'value too long' and before insert/update trigger |
Date: | 2017-08-24 05:41:26 |
Message-ID: | 00ca01d31c9b$a7b98be0$f72ca3a0$@swisspug.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Kevin Golding
> Sent: Donnerstag, 24. August 2017 07:08
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] 'value too long' and before insert/update trigger
>
> Hi all
> I'm currently migrating a legacy Informix 4gl application to run on PostgreSQL (v9.5.8)
>
> There are errors occurring because sometimes the application tries to insert/update values longer than the
> definition of the database column.
> The error message is eg. "value too long for type character(20)".
>
> The behaviour under Informix is to silently truncate the saved value, but with PostgreSQL it crashes our application
> program. Obviously the short answer is to modify the application so that it does not attempt to save values that are
> too long, but with 1000+ programs and approx. 8000 insert/update statements to review this will take a lot of time.
If there is no good reason to force the length of the field to be a maximum of 20 characters, you may change the data type to text.
> I was hoping to emulate the Informix behaviour by creating triggers to run before each insert/update that would
> check the length of the values being saved and truncate if too long. However I'm still getting the error messages.
> Presumably the length validation is being done before the trigger is run. Is there some way this could be changed so
> the trigger happens first?
When you create the trigger you can define if the function must be called before or after the action.
Something like:
CREATE TRIGGER name
BEFORE INSERT OR UPDATE ON table_name
FOR EACH ROW EXECUTE PROCEDURE ...;
In the function you must then replace the value of the string before you return the NEW record to the calling process:
[...]
NEW.variable_name := substr(NEW.variable_name,1,20);
[...]
RETURN NEW;
Hope this helps.
Bye
Charles
> I've seen examples that involve changing the char type columns to text, but this changes the semantics of string
> comparisons with regard to trailing spaces, and I'm concerned that this might cause subtle and hard to find problems
> within the application.
>
> Thanks
> Kevin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2017-08-24 05:48:06 | Re: 'value too long' and before insert/update trigger |
Previous Message | Kevin Golding | 2017-08-24 05:08:22 | 'value too long' and before insert/update trigger |