3

I'm migrating a legacy application's database from Postgres 8.1 to Postgres 9.3

In order to avoid compatibility issues, I'd like to keep the standard_conforming_strings parameter to it's default value in 8.1: off.

So, I create the databases using following in the schema:

SET standard_conforming_strings = off;

But a pg_dump of the database reveals that Postgres has changed the value to on.

SET standard_conforming_strings = on;

How can I prevent this?

3

How exactly did you set standard_conforming_strings?

If you did that in your session, it only applies to that session. For a permanent setting change your postgresql.conf and reload.

Also, a pg_dump cannot reveal anything. The setting in the dump only applies to the dump. To see the current setting of your database, run

SHOW standard_conforming_strings;

Or:

SELECT * FROM pg_settings WHERE name = 'standard_conforming_strings';

Here you also see where the current value is from and whether it's the default etc.

Generally, It's a very bad idea to try and keep standard_conforming_strings off. This was changed for a good reason. It's going to bite you sooner or later. Update to the current (superior) standard.

1

you can use the alter command.

  ALTER USER your_login_name SET STANDARD_CONFORMING_STRINGS TO OFF;

ALTER DATABASE can also be used here, but as standard conforming strings are simpler to use it's probably best to keep the old-style restricted to legacy users.

a better approach is to use e to prefix escaped strings if you ever need them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.