Next: SHOW
Up: Reference Manual
Previous: SELECT INTO
Subsections
SET
SET -- Set run-time parameters for session
SET variable { TO | = } { value |
'value ' | DEFAULT }
SET CONSTRAINTS { ALL | constraintlist } mode
SET TIME ZONE { 'timezone ' | LOCAL | DEFAULT
}
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE
}
- variable
- Settable global parameter.
- value
- New value of parameter. DEFAULT can be used to specify
resetting the parameter to its default value. Lists of strings are
allowed, but more complex constructs may need to be single or double
quoted.
The possible variables and allowed values are:
- CLIENT_ENCODING | NAMES
- Sets the multibyte client encoding.
Parameters are:
- value
- Sets the multibyte client encoding to value.
The specified encoding must be supported by the backend.
This option is only available if MULTIBYTE support was enabled during
the configure step of building Postgres.
- 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
- same as NonEuropean
- DEFAULT
- restores the default values (ISO)
Date format initialization may be done by:
- Setting the PGDATESTYLE environment variable. If PGDATESTYLE is set
in the frontend environment of a client based on libpq, libpq will
automatically set DATESTYLE to the value of PGDATESTYLE during connection
start-up.
- Running postmaster using the option -o -e to set dates to the
European convention.
- SEED
- Sets the internal seed for the random number generator.
- value
- The value for the seed to be used by the random catalog
function. Significant values are floating point numbers between 0
and 1, which are then multiplied by RAND_MAX. This product will silently
overflow if a number outside the range is used.
The seed can also be set by invoking the setseed SQL function:
SELECT setseed(value);
This option is only available if MULTIBYTE support was enabled during
the configure step of building Postgres.
- SERVER_ENCODING
- Sets the multibyte server encoding to:
- value
- The identifying value for the server encoding.
This option is only available if MULTIBYTE support was enabled during
the configure step of building Postgres.
- CONSTRAINTS
- SET CONSTRAINTS affects the behavior of constraint evaluation
in the current transaction. SET CONSTRAINTS, specified in SQL3, has
these allowed parameters:
- constraintlist
- Comma separated list of deferrable constraint
names.
- mode
- The constraint mode. Allowed values are DEFERRED and
IMMEDIATE.
In IMMEDIATE mode, foreign key constraints are checked at the end
of each query.
In DEFERRED mode, foreign key constraints marked as DEFERRABLE are
checked only at transaction commit or until its mode is explicitly
set to IMMEDIATE. This is actually only done for foreign key constraints,
so it does not apply to UNIQUE or other constraints.
- TIME ZONE, TIMEZONE
- The possible values for time zone depends
on your operating system. For example, on Linux /usr/lib/zoneinfo
contains the database of time zones.
Here are some valid values for time zone:
- PST8PDT
- set the time zone for California
- Portugal
- set time zone for Portugal.
- 'Europe/Rome'
- set time zone for Italy.
- DEFAULT
- set time zone to your local time zone (value of the TZ environment
variable).
If an invalid time zone is specified, the time zone becomes GMT (on
most systems anyway).
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.
If the PGTZ environment variable is set in the frontend environment
of a client based on libpq, libpq will automatically set TIMEZONE
to the value of PGTZ during connection start-up.
- 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:
- PG_OPTIONS
- Sets various backend parameters.
- RANDOM_PAGE_COST
- Sets the optimizer's estimate of the cost of
a nonsequentially fetched disk page. This is measured as a multiple
of the cost of a sequential page fetch.
- float8
- Set the cost of a random page access to the specified
floating-point value.
- CPU_TUPLE_COST
- Sets the optimizer's estimate of the cost of processing
each tuple during a query. This is measured as a fraction of the cost
of a sequential page fetch.
- float8
- Set the cost of per-tuple CPU processing to the specified
floating-point value.
- CPU_INDEX_TUPLE_COST
- Sets the optimizer's estimate of the cost
of processing each index tuple during an index scan. This is measured
as a fraction of the cost of a sequential page fetch.
- float8
- Set the cost of per-index-tuple CPU processing to
the specified floating-point value.
- CPU_OPERATOR_COST
- Sets the optimizer's estimate of the cost of
processing each operator in a WHERE clause. This is measured as a
fraction of the cost of a sequential page fetch.
- float8
- Set the cost of per-operator CPU processing to the
specified floating-point value.
- EFFECTIVE_CACHE_SIZE
- Sets the optimizer's assumption about the
effective size of the disk cache (that is, the portion of the kernel's
disk cache that will be used for Postgres data files). This is measured
in disk pages, which are normally 8Kb apiece.
- float8
- Set the assumed cache size to the specified floating-point
value.
- ENABLE_SEQSCAN
- Enables or disables the planner's use of sequential
scan plan types. (It's not possible to suppress sequential scans entirely,
but turning this variable OFF discourages the planner from using one
if there is any other method available.)
- ON
- enables use of sequential scans (default setting).
- OFF
- disables use of sequential scans.
- ENABLE_INDEXSCAN
- Enables or disables the planner's use of index
scan plan types.
- ON
- enables use of index scans (default setting).
- OFF
- disables use of index scans.
- ENABLE_TIDSCAN
- Enables or disables the planner's use of TID scan
plan types.
- ON
- enables use of TID scans (default setting).
- OFF
- disables use of TID scans.
- ENABLE_SORT
- Enables or disables the planner's use of explicit sort
steps. (It's not possible to suppress explicit sorts entirely, but
turning this variable OFF discourages the planner from using one if
there is any other method available.)
- ON
- enables use of sorts (default setting).
- OFF
- disables use of sorts.
- ENABLE_NESTLOOP
- Enables or disables the planner's use of nested-loop
join plans. (It's not possible to suppress nested-loop joins entirely,
but turning this variable OFF discourages the planner from using one
if there is any other method available.)
- ON
- enables use of nested-loop joins (default setting).
- OFF
- disables use of nested-loop joins.
- ENABLE_MERGEJOIN
- Enables or disables the planner's use of mergejoin
plans.
- ON
- enables use of merge joins (default setting).
- OFF
- disables use of merge joins.
- ENABLE_HASHJOIN
- Enables or disables the planner's use of hashjoin
plans.
- ON
- enables use of hash joins (default setting).
- OFF
- disables use of hash joins.
- GEQO
- Sets the threshold for using the genetic optimizer algorithm.
- ON
- enables the genetic optimizer algorithm for statements with 11
or more tables. (This is also the DEFAULT setting.)
- 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.
See the chapter on GEQO in the Programmer's Guide for more information
about query optimization.
If the PGGEQO environment variable is set in the frontend environment
of a client based on libpq, libpq will automatically set GEQO to the
value of PGGEQO during connection start-up.
- KSQO
- Key Set Query Optimizer causes the query planner to
convert queries whose WHERE clause contains many OR'ed AND clauses
(such as "WHERE (a=1 AND b=2) OR (a=2 AND b=3) ...")
into a UNION query. This method can be faster than the default implementation,
but it doesn't necessarily give exactly the same results, since UNION
implicitly adds a SELECT DISTINCT clause to eliminate identical output
rows. KSQO is commonly used when working with products like Microsoft
Access, which tend to generate queries of this form.
- ON
- enables this optimization.
- OFF
- disables this optimization (default setting).
- DEFAULT
- Equivalent to specifying SET KSQO=OFF.
The KSQO algorithm used to be absolutely essential for queries with
many OR'ed AND clauses, but in Postgres 7.0 and later the standard
planner handles these queries fairly successfully.
- MAX_EXPR_DEPTH
- Sets the maximum expression nesting depth that
the parser will accept. The default value is high enough for any normal
query, but you can raise it if you need to. (But if you raise it too
high, you run the risk of backend crashes due to stack overflow.)
- integer
- Maximum depth.
- SET VARIABLE
- Message returned if successful.
- NOTICE: Bad value for variable (value )
- If the
command fails to set the specified variable.
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.
Set the style of date to ISO (no quotes on the argument is required):
SET DATESTYLE TO ISO;
Enable GEQO for queries with 4 or more tables (note the use of single
quotes to handle the equal sign inside the value argument):
SET GEQO = 'ON=4';
Set GEQO to default:
SET GEQO = DEFAULT;
Set the time zone for Berkeley, California, using double quotes to
preserve the uppercase attributes of the time zone specifier:
SET TIME ZONE "PST8PDT"; SELECT CURRENT_TIMESTAMP AS today;
today
------------------------
1998-03-31 07:41:21-08
Set the time zone for Italy (note the required single or double quotes
to handle the special characters):
SET TIME ZONE 'Europe/Rome';
SELECT CURRENT_TIMESTAMP AS today;
today
------------------------
1998-03-31 17:41:31+02
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 }
Next: SHOW
Up: Reference Manual
Previous: SELECT INTO
Bruce Momjian
2005-04-21