Supported Versions: Current (16) / 15 / 14 / 13 / 12
Development Versions: devel
Unsupported versions: 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1
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

Synopsis

SET variable { TO | = } { value | 'value' | DEFAULT }
SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
  

Inputs

variable

A settable run-time 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.

Description

The SET command changes run-time configuration parameters. The following parameters can be altered:

CLIENT_ENCODING, NAMES

Sets the multibyte client encoding. The specified encoding must be supported by the backend.

This option is only available if Postgres is build with multibyte support.

DATESTYLE

Choose the date/time representation style. Two separate settings are made: the default date/time output and the interpretation of ambiguous input.

The following are date/time output styles:

ISO

Use ISO 8601-style dates and times (YYYY-MM-DD HH:MM:SS). This is the default.

SQL

Use Oracle/Ingres-style dates and times. Note that this style has nothing to do with SQL (which mandates ISO 8601 style), the naming of this option is a historical accident.

Postgres

Use traditional Postgres format.

German

Use dd.mm.yyyy for numeric date representations.

The following two options determine both a substyle of the "SQL" and "Postgres" output formats and the preferred interpretation of ambiguous date input.

European

Use dd/mm/yyyy for numeric date representations.

NonEuropean, US

Use mm/dd/yyyy for numeric date representations.

A value for SET DATESTYLE can be one from the first list (output styles), or one from the second list (substyles), or one from each separated by a comma.

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.

The DateStyle option is really only intended for porting applications. To format your date/time values to choice, use the to_char family of functions.

SEED

Sets the internal seed for the random number generator.

value

The value for the seed to be used by the random function. Allowed values are floating point numbers between 0 and 1, which are then multiplied by 2^31-1. 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);
            
SERVER_ENCODING

Sets the multibyte server encoding.

This option is only available if Postgres was built with multibyte support.

TIME ZONE, TIMEZONE

The possible values for time zone depends on your operating system. For example, on Linux /usr/share/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.

LOCAL, DEFAULT

Set the time zone to your local time zone (the one that your operating system defaults to).

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

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.

An extended list of other run-time parameters can be found in the Administrator's Guide.

Use SHOW to show the current setting of a parameters.

Diagnostics

SET VARIABLE

Message returned if successful.

ERROR: not a valid option name: name

The parameter you tried to set does not exist.

ERROR: permission denied

You must be a superuser to have access to certain settings.

ERROR: name can only be set at start-up

Some parameters are fixed once the server is started.

Examples

Set the style of date to traditional Postgres with European conventions:

SET DATESTYLE TO Postgres,European;
Set the time zone for Berkeley, California, using double quotes to preserve the uppercase attributes of the time zone specifier (note that the date/time format is ISO here):
 
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

Compatibility

SQL92

The second syntax shown above (SET TIME ZONE) attempts to mimic SQL92. However, SQL allows only numeric time zone offsets. All other parameter settings as well as the first syntax shown above are a Postgres extension.