Postgres 9.3 changes the standard_conforming_strings value

Question

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?

Answer 1

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 does not 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 get more details from pg_settings:

SELECT * FROM pg_settings WHERE name = 'standard_conforming_strings';

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

Answer 2

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.