Skip to content

Instantly share code, notes, and snippets.

@hmarr
Created October 29, 2015 12:02
Show Gist options
  • Save hmarr/d888cd405dd0d699e05a to your computer and use it in GitHub Desktop.
Save hmarr/d888cd405dd0d699e05a to your computer and use it in GitHub Desktop.
Safely updating standard_conforming_strings - pg8.1 vs pg8.2

Postgres 8.1

$ ./bin/psql template1
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# select name, setting, context from pg_settings where name = 'standard_conforming_strings';
            name             | setting | context
-----------------------------+---------+----------
 standard_conforming_strings | off     | internal
(1 row)

template1=# update pg_settings set setting = 'on' where name = 'standard_conforming_strings';
ERROR:  parameter "standard_conforming_strings" cannot be changed
template1=# update pg_settings set setting = 'on' where name = 'standard_conforming_strings' and context = 'user';
 set_config
------------
(0 rows)

Postgres 8.2

$ ./bin/psql template1
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=# select name, setting, context from pg_settings where name = 'standard_conforming_strings';
            name             | setting | context
-----------------------------+---------+---------
 standard_conforming_strings | off     | user
(1 row)

template1=# update pg_settings set setting = 'on' where name = 'standard_conforming_strings';
 set_config
------------
 on
(1 row)

UPDATE 0
template1=# update pg_settings set setting = 'on' where name = 'standard_conforming_strings' and context = 'user';
 set_config
------------
 on
(1 row)

UPDATE 0
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment