SET

SET — Change a run-time parameter

Synopsis

SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }

SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

Description

The SET command changes run-time configuration parameters. Many parameters can be changed on the fly with SET (but some require superuser privileges to change, and some cannot be changed after the server or session starts).

SET only affects the value used by the current session.

If SET (or equivalently SET SESSION) is issued within a transaction that is later aborted, the effects of the SET command disappear when the transaction is rolled back. Once the enclosing transaction is committed, the effects persist until the end of the session (unless overridden by another SET).

The effects of SET LOCAL last only until the end of the current transaction, whether the transaction is committed or not. A special case is when SET is followed by SET LOCAL within a transaction: the SET LOCAL value will be visible until the end of the transaction, but afterwards (if the transaction is committed) the SET value will take effect.

The effects of SET or SET LOCAL are also erased by rolling back to a savepoint that precedes them.

If SET LOCAL is used within a function and the function also has a SET option for the same variable (see CREATE FUNCTION), the effects of the SET LOCAL command disappear when the function exits. That is, the value at the time the function was called will be restored. This allows SET LOCAL to be used to dynamically or repeatedly change a parameter within a function, while still conveniently using the SET option to save and restore the caller's value. However, a regular SET command overrides any SET option of the enclosing function, and its effects persist unless rolled back.

Parameters

SESSION

Specifies that the command takes effect for the current session (this is the default).

LOCAL

Specifies that the command takes effect only for the current transaction. After COMMIT or ROLLBACK, the session-level setting takes effect again. Issuing this parameter outside a transaction block emits a warning and has no effect.

configuration_parameter

The name of a settable run-time parameter.

value

The new value of the parameter. Depending on the specific parameter, values can be specified as string constants, identifiers, numbers, or a comma-separated list of any of these. Write DEFAULT to specify resetting the parameter to its default value (i.e., the value it would have had if no SET command had been executed in the current session).

There are also parameters that can only be set with the SET command or that have special syntax:

SCHEMA

SET SCHEMA 'value' is an alias for SET search_path TO value. Only one schema can be specified with this syntax.

NAMES

SET NAMES value is an alias for SET client_encoding TO value.

SEED

Sets the internal seed for the random number generator (the random function). Allowed values are floating-point numbers between -1 and 1, which are multiplied by 2^31-1.

The seed can also be set by calling the function setseed:

SELECT setseed(value);

TIME ZONE

SET TIME ZONE value is an alias for SET timezone TO value. The syntax SET TIME ZONE allows special syntax for time zone specifications. Here are examples of valid values:

'PST8PDT'

The time zone for Berkeley, California.

'Europe/Rome'

The time zone for Italy.

-7

The time zone 7 hours west of UTC (equivalent to PDT). Positive values are east of UTC.

INTERVAL '-08:00' HOUR TO MINUTE

The time zone 8 hours west of UTC (equivalent to PST).

LOCAL

DEFAULT

Sets the time zone to your local time zone (i.e., the server's default value for timezone).

Time zone settings given as numbers or intervals are internally translated to POSIX time zone syntax. For example, after SET TIME ZONE -7, SHOW TIME ZONE will report <-07>+07.

Notes

The function set_config provides equivalent functionality. Additionally, the pg_settings system view can be updated to perform the equivalent of SET.

Examples

# Set the schema search path:

SET search_path TO my_schema, public;

# Set the time zone to Berkeley, California:

SET TIME ZONE 'PST8PDT';

# Set the time zone to Italy:

SET TIME ZONE 'Europe/Rome';

See Also

RESET, SHOW