Hi > -----Original Message----- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Kevin Golding > Sent: Donnerstag, 24. August 2017 07:08 > To: pgsql-general@postgresql.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@postgresql.org) To make > changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
