set(5)

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 in the documentation 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 ver-
       sions 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 transac-
       tion, 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 trans-
              action will end immediately.

       configuration_parameter
              Name of a settable run-time parameter. Available parameters  are
              documented in in the documentation and below.

       value  New  value  of parameter. Values can be specified as string con-
              stants, 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  in  the  documenta-
       tion,  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 in the documentation for more information about time zones.

NOTES

       The function set_config provides equivalent functionality. See  in  the
       documentation.

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  Post-
       greSQL extensions.

SEE ALSO

       RESET [reset(5)], SHOW [show(l)]

SQL - Language Statements         2008-06-08                             SET()