You are herePostgreSQL 9.1 Text Fields Are Getting Messed Up

PostgreSQL 9.1 Text Fields Are Getting Messed Up

By steve - Posted on 10 April 2014

I have a database for a client that uses TEXT fields in a postgresql database to save RTF letters with special formatting like bold or italics. The system has used a series of postgresql versions like 7.4, 8.1, and so on.

We don't update very often because postgresql is very fast and very stable. In fact, we usually only upgrade when forced to move to a new version of Ubuntu LTS or something like that where it is easier to go with the current version than it is to try to keep the old one up to date.

Anyway, we upgraded from 8.1 to 9.1 a few weeks ago (using typical pg_dump and pg_restore steps) and all of my OQ (Operational Qualification) testing indicated the data all made it over just fine, the system was working, rows were updating, and everything was working just peachy.

After a few weeks, the LETTERS table need to be updated with new letters and that is when the trouble started...

It was strange, but we could add new rows, but we couldn't update existing rows.

The rows we added, though, didn't work correctly in the mail merge process (Crystal Reports 9).

It took a lot of sniffing around, but I finally figured out what happened.

Starting with 9.1, the default setting for a certain parameter changed from OFF to ON. So open up postgresql.conf and change the setting to this:

standard_conforming_strings = off

The fix was to add the line (default is on, but the setting is actually remarked out following postgresql conf logic) with = off for the value, then reload the service:

/etc/init.d/postgresql reload

That's it!! Problem solved. The database now went back to the previous behavior and worked exactly as expected.

Here is the notification that the default changed.

Did this help you? You can help me!

Did you find this information helpful? You can help me back by linking to this page, purchasing from my sponsors, or posting a comment!

+One me on Google:

Follow me on twitter:


Affiliation Badges