PostgreSQL 8.2.18 Documentation
Prev | Fast Backward |  | Fast Forward | Next

# SET

## Name

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 of the run-time parameters listed in Chapter 17 can be changed on-the-fly with `SET`. (But some require superuser privileges to change, and others cannot be changed after server or session start.) `SET` only affects the value used by the current session.

If `SET` or `SET SESSION` is issued within a transaction that is later aborted, the effects of the `SET` command disappear when the transaction is rolled back. (This behavior represents a change from PostgreSQL versions prior to 7.3, where the effects of `SET` would not roll back after a later error.) Once the surrounding transaction is committed, the effects will persist until the end of the session, unless overridden by another `SET`.

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

## Parameters

`SESSION`

  Specifies that the command takes effect for the current session. (This is the default if neither `SESSION` nor `LOCAL` appears.)

`LOCAL`

  Specifies that the command takes effect for only the current transaction. After `COMMIT` or `ROLLBACK`, the session-level setting takes effect again. Note that `SET LOCAL` will appear to have no effect if it is executed outside a `BEGIN` block, since the transaction will end immediately.

`configuration_parameter`

  Name of a settable run-time parameter. Available parameters are documented in Chapter 17 and below.

`value`

  New value of parameter. Values can be specified as string constants, identifiers, numbers, or comma-separated lists of these. `DEFAULT` can be used to specify resetting the parameter to its default value.

Besides the configuration parameters documented in Chapter 17, there are a few that can only be adjusted using the `SET` command or that have a special 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 function `random`). Allowed values are floating-point numbers between 0 and 1, which are then multiplied by 231-1.

  The seed can also be set by invoking 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 the time zone specification. 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 from UTC (equivalent to PDT). Positive values are east from UTC.

  `INTERVAL '-08:00' HOUR TO MINUTE`

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

  `LOCAL`
  `DEFAULT`

    Set the time zone to your local time zone (the one that the server's operating system defaults to).

  See Section 8.5.3 for more information about time zones.

## Notes

The function `set_config` provides equivalent functionality. See Section 9.20.

## Examples

Set the schema search path:

    SET search_path TO my_schema, public;

Set the style of date to traditional POSTGRES with "day before month" input convention:

    SET datestyle TO postgres, dmy;

Set the time zone for Berkeley, California:

    SET TIME ZONE 'PST8PDT';

Set the time zone for Italy:

    SET TIME ZONE 'Europe/Rome';

## Compatibility

`SET TIME ZONE` extends syntax defined in the SQL standard. The standard allows only numeric time zone offsets while PostgreSQL allows more flexible time-zone specifications. All other `SET` features are PostgreSQL extensions.

## See Also

RESET, SHOW

Prev | Home | Next
SELECT INTO | Up | SET CONSTRAINTS