Computer Science
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
Back to the index
