Unsupported versions: 7.0 / 6.5 / 6.4
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

SET

Name

SET — Set run-time parameters for session
SET variable { TO | = } { 'value' | DEFAULT }
SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZED }
  

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 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

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 -o -e option to set dates to the European convention. Note that this affects only the 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 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'

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 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

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 specified 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 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.

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 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.

Notes

The SET variable statement is a Postgres language extension.

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 SET variable in SQL92 (except for 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 }