SET (l)
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 }
variable
Settable global parameter.
value
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 supported by the backend.
DEFAULT
Sets the multi-byte client encoding to the default value.
This is only enabled if multi-byte was specified to configure.
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 representations.
NonEuropean
use mm/dd/yyyy for numeric date representations.
German
use dd.mm.yyyy for numeric date representations.
US
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
This is only enabled if multi-byte was specified to configure.
TIMEZONE
The possible values for timezone depends on your operating
system. For example on Linux /usr/lib/zoneinfo contains the
database of timezones.
Here are some valid values for timezone:
'PST8PDT'
set the timezone for California
'Portugal'
set time zone for Portugal.
'Europe/Rome'
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 timezone
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 transaction.
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
The frontend may be initialized by setting the PGCOSTHEAP
environment variable.
COST_INDEX
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
environment 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.
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 during
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 offset.
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.
The SET variable
statement is a Postgres language extension.
Refer to SHOW and RESET to
display or reset the current values.
USAGE
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
There is no general
SET variable
in SQL92 (with the exception of
SET TRANSACTION ISOLATION LEVEL).
The SQL92 syntax for SET TIME ZONE
is slightly different,
allowing only a single integer value for time zone specification:
SET TIME ZONE { interval_value_expression | LOCAL }
| 
