SET

Synopsis

Use the SET statement to change the value of a run-time parameter.

Syntax

Grammar
Diagram

set ::= SET [ SESSION | LOCAL ] { run_time_parameter { TO | = } 
                                  { value | DEFAULT }
                                  | TIME ZONE 
                                    { timezone | LOCAL | DEFAULT } }

set

Semantics

The parameter values that you set with this statement apply just in the scope of a single session and for no longer than the session's duration. It's also possible to set the default values for such parameters at the level of the entire cluster or at the level of a particular database. For example:

alter database demo set timezone = 'America/Los_Angeles';

See ALTER DATABASE.

SESSION

Specify that the command affects only the current session.

LOCAL

Specify that the command affects only the current transaction. After COMMIT or ROLLBACK, the run-time parameter takes effect again.

run_time_parameter

Specify the name of a mutable run-time parameter.

You can inspect the current value of a run-time parameter with the current_setting() built-in function, for example:

select current_setting('search_path');

The term of art setting is used as a convenient shorthand for run-time parameter. Many run-time parameters are system-defined. A system-defined run-time parameter is spelled using only latin letters and underscores. And, in any particular PostgreSQL version, the list of system-defined run-time parameters is fixed.

The show ... SQL statement is a shorthand for select current_setting(...), for example:

show search_path;

The show statement has a special variant show all, not available when you use select current_setting(...), that lists the current value of every system-defined run-time parameter. (An alternative way to see this list, with some useful accompanying information for each setting, is to query the pg_settings catalog view.)

value

Specify the value of parameter.

User-defined run-time parameters

You can also create a user-defined run-time parameter, on the fly, by spelling it suitably. The duration of a user-defined run-time parameter never exceeds that of the session. The spelling must include a period. And, as long as you double-quote in the set statement, it can contain any combination of arbitrary characters. Try this:

set min.værelse17 = 'stue';
set "MIN.VÆRELSE17@" = 'kjøkken';
set "^我的.爱好" = '跳舞';
select
  current_setting('min.værelse17')  as s1,
  current_setting('MIN.VÆRELSE17@') as s2,
  current_setting('^我的.爱好')      as s3;

This is the result:

  s1  |   s2    |  s3  
------+---------+------
 stue | kjøkken | 跳舞

Notice that show all and querying pg_settings never lists user-defined run-time parameters.

Run-time parameters respect transactional semantics.

Try this test:

set search_path = pg_catalog, pg_temp;
start transaction;
set search_path = some_schema, pg_catalog, pg_temp;
set my.value = 'something';
select current_setting('search_path');
select current_setting('my.value');
rollback;
select current_setting('search_path');
select '>'||current_setting('my.value')||'<';

The test outputs this:

 some_schema, pg_catalog, pg_temp

followed by this:

 something

when the transaction is ongoing and this:

 pg_catalog, pg_temp