SET(l)                                                     SET(l)

NAME
       SET - Set run-time parameters for session

SYNOPSIS
       SET variable { TO | = } { 'value' | DEFAULT }
       SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
       SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }

   INPUTS
       variable
              Settable global parameter.

       value  New value of parameter.

       The possible variables and allowed values are:

       CLIENT_ENCODING | NAMES
              Sets  the  multi-byte  client  encoding. Parameters
              are:

              value  Sets  the  multi-byte  client  encoding   to
                     value.   The specified encoding must be sup-
                     ported by the backend.

              DEFAULT
                     Sets the multi-byte client encoding  to  the
                     default value.

       This  is  only enabled if multi-byte was specified to con-
       figure.

       DateStyle
              Set the date/time representation style. Affects the
              output  format, and in some cases it can affect the
              interpretation of input.

              ISO    use ISO 8601-style dates and times

              SQL    use Oracle/Ingres-style dates and times

              Postgres
                     use traditional Postgres format

              European
                     use dd/mm/yyyy for numeric date  representa-
                     tions.

              NonEuropean
                     use  mm/dd/yyyy for numeric date representa-
                     tions.

              German use dd.mm.yyyy for numeric date  representa-
                     tions.

              US     same as 'NonEuropean'

              DEFAULT
                     restores the default values ('US,Postgres')

       Date format initialization may be done by:

       Setting the PGDATESTYLE environment variable.

       Running  postmaster using the option -o -e to set dates to
       the European convention.  Note that this affects only some
       combinations  of date styles; for example the ISO style is
       not affected by this parameter.

       Changing variables in src/backend/utils/init/globals.c.

       The variables in globals.c which can be changed are:

       bool EuroDates = false | true

       int  DateStyle  =  USE_ISO_DATES  |  USE_POSTGRES_DATES  |
       USE_SQL_DATES | USE_GERMAN_DATES

       SERVER_ENCODING
              Sets the multi-byte server encoding

              value  Sets the multi-byte server encoding.

              DEFAULT
                     Sets the multi-byte server encoding.

       This  is  only enabled if multi-byte was specified to con-
       figure.

       TIMEZONE
              The possible values for timezone  depends  on  your
              operating    system.    For    example   on   Linux
              /usr/lib/zoneinfo contains the  database  of  time-
              zones.

              Here are some valid values for timezone:

              'PST8PDT'
                     set the timezone for California

              'Portugal'
                     set time zone for Portugal.

              'Europe/Rome'
                     set time zone for Italy.

              DEFAULT
                     set  time zone to your local timezone (value
                     of the TZ environment variable).

       If an invalid  time  zone  is  specified,  the  time  zone
       becomes GMT (on most systems anyway).

       A  frontend which uses libpq may be initialized by setting
       the PGTZ environment variable.

       The second syntax shown above, allows one to set the time-
       zone  with  a  syntax similar to SQL92 SET TIME ZONE.  The
       LOCAL keyword is just an alternate  form  of  DEFAULT  for
       SQL92 compatibility.

       TRANSACTION ISOLATION LEVEL
              Sets  the  isolation level for the current transac-
              tion.

              READ COMMITTED
                     The current transaction  queries  read  only
                     rows  committed  before  a query began. READ
                     COMMITTED is the default.

                     Note: SQL92 standard  requires  SERIALIZABLE
                     to be the default isolation level.

              SERIALIZABLE
                     The  current  transaction  queries read only
                     rows committed before  first  DML  statement
                     (SELECT/INSERT/DELETE/UPDATE/FETCH/COPY_TO)
                     was executed in this transaction.

       There are also several internal or optimization parameters
       which can be specified by the SET command:

       COST_HEAP
              Sets the default cost of a heap scan for use by the
              optimizer.

              float4 Set the cost of a heap scan to the specified
                     floating point value.

              DEFAULT
                     Sets  the cost of a heap scan to the default
                     value.

       The frontend may be initialized by setting the  PGCOSTHEAP
       environment variable.

       COST_INDEX
              Sets  the  default cost of an index scan for use by
              the optimizer.

              float4 Set the cost of an index scan to the  speci-
                     fied floating point value.

              DEFAULT
                     Sets  the  cost  of  an  index  scan  to the
                     default value.

       The frontend may be initialized by setting the PGCOSTINDEX
       environment variable.

       GEQO   Sets  the threshold for using the genetic optimizer
              algorithm.

              ON     enables the genetic optimizer algorithm  for
                     statements with 6 or more tables.

              ON=#   Takes  an  integer  argument  to  enable the
                     genetic optimizer algorithm  for  statements
                     with # or more tables in the query.

              OFF    disables the genetic optimizer algorithm.

              DEFAULT
                     Equivalent to specifying SET GEQO='ON'

       This  algorithm  is  on  by  default,  which used GEQO for
       statements of eleven or more tables.  (See the chapter  on
       GEQO in the Programmer's Guide for more information).

       The frontend may be initialized by setting PGGEQO environ-
       ment variable.

       It may be useful when joining  big  relations  with  small
       ones.  This algorithm is off by default.  It's not used by
       GEQO anyway.

       KSQO   Key Set Query Optimizer forces the query  optimizer
              to optimize repetative OR clauses such as generated
              by MicroSoft Access:

              ON     enables this optimization.

              OFF    disables this optimization.

              DEFAULT
                     Equivalent to specifying SET KSQO='OFF'.

       It may be useful when joining  big  relations  with  small
       ones.  This algorithm is off by default.  It's not used by
       GEQO anyway.

       The frontend may be  initialized  by  setting  the  PGKSQO
       environment variable.

   OUTPUTS
       SET VARIABLE
              Message returned if successfully.

       WARN: Bad value for variable (value)
              If the command fails to set the specified variable.

DESCRIPTION
       SET will modify configuration parameters for variable dur-
       ing a session.

       Current  values can be obtained using SHOW, and values can
       be restored to the defaults using RESET.   Parameters  and
       values  are case-insensitive. Note that the value field is
       always specified as a string, so is  enclosed  in  single-
       quotes.

       SET TIME ZONE changes the session's default time zone off-
       set.  An SQL-session always begins with an initial default
       time  zone offset.  The SET TIME ZONE statement is used to
       change the default time zone offset for  the  current  SQL
       session.

   NOTES
       The  SET  variable statement is a Postgres language exten-
       sion.

       Refer to SHOW and RESET to display or  reset  the  current
       values.

USAGE
       Set the style of date to ISO:

       SET DATESTYLE TO 'ISO';

       Enable GEQO for queries with 4 or more tables:

       SET GEQO ON=4;

       Set GEQO to default:

       SET GEQO = DEFAULT;

       Set the timezone for Berkeley, California:

       SET TIME ZONE 'PST8PDT';
       SELECT CURRENT_TIMESTAMP AS today;

          today
          ----------------------
          1998-03-31 07:41:21-08

       Set the timezone for Italy:

       SET TIME ZONE 'Europe/Rome';
       SELECT CURRENT_TIMESTAMP AS today;

          today
          ----------------------
          1998-03-31 17:41:31+02

COMPATIBILITY
   SQL92
       There is no general SET variable in SQL92 (with the excep-
       tion of SET TRANSACTION ISOLATION LEVEL).  The SQL92  syn-
       tax for SET TIME ZONE is slightly different, allowing only
       a single integer value for time zone specification:

       SET TIME ZONE { interval_value_expression | LOCAL }

SQL - Language Statements 15 August 1999                        1