Hi 2017-08-24 7:08 GMT+02:00 Kevin Golding <kgold...@axessgroup.com.au>:
> 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. > > 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? > > 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 > The most simple way is using custom type with similar behave like Informix - I did it with port from MySQL http://okbob.blogspot.cz/2009/08/mysql-functions-for-postgresql.html you can use google translator - the article is in Czech language - but can be translated Regards Pavel > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >