SET variable { TO | = } { value | 'value' | DEFAULT }
SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
Parameters
variable
The name of the runtime variable you are setting.
value
A new value for the specified variable. Use DEFAULT to reset the variable to its default value.
timezone
The time zone of the client. The following are a few valid time zone values:
PST8PDT
The Pacific Standard/Daylight Savings Time (GMT offset by 8 hours).
EST5EDT
The Eastern Standard/Daylight Savings Time (GMT offset by 5 hours).
NZST13NZDT
The standard/daylight savings time zone in New Zealand (GMT offset by 13 hours).
LOCAL
The clause to set the time zone to the local system's configured time zone.
DEFAULT
The clause to set a variable value, or a time zone value, to its default.
Results
SET VARIABLE
The message returned when a variable is successfully set.
ERROR: not a valid option name:(name)
The error returned if you try to set a variable that doesn't exist.
ERROR: permission denied
The error returned if you do not have adequate permissions to alter the specified variable.
ERROR: name can only be set at start-up
The error returned if you attempt to set a variable that can only set set upon startup.
Description
Use the SET command to modify PostgreSQL runtime configuration variables. The following variable can be altered:
CLIENT_ENCODING
The multibyte client encoding scheme (if enabled in PostgreSQL).
DATESTYLE
This variable sets the date and time representation style. When setting this variable, you can choose one format from the normal output styles, one of the two substyles, or both an output style and a substyle. Initialize the format by manually changing the PGDATESTYLE environment variable. You can also initialize the format using postmaster command options. For example, run postmaster using -o "-e" to set dates to the European format (see Chapter 9 for more about the options available to postmaster).
The following are valid date and time output styles:
ISO
The ISO-8601–style date and time formatting. Date and time are displayed as YYYY-MM-DD HH:MM:SS. This is the default style.
SQL
The Oracle/Ingres–style date and time formatting. Despite this format's label, it is not the SQL default; SQL uses ISO–8601 style formatting.
Postgres
The traditional PostgreSQL date and time formatting.
German
The traditional German-style formatting. Numeric date representations are displayed as DD.MM.YYYY.
European
The standard European-style formatting. This is a substyle of the SQL and PostgreSQL styles. Numeric date representations are displayed as DD/MM/YYYY.
NonEuropean, US
The standard United States-style formatting. This is a substyle of the SQL and Postgres styles. Numeric date representations are displayed as MM/DD/YYYY.
SEED
This variable sets the internal seed for the PostgreSQL random number generator, which is used by the
random() function. Allowed values are floating point numbers between 0 and 1. The number you supply
is then multiplied by 2^30.
Alternatively, you may set the seed by calling the SQL setseed() function, with a single argument of type double precision.
SERVER_ENCODING
The server's default multibyte encoding (if enabled in PostgreSQL).
Examples
The following example sets the DATESTYLE variable to use traditional PostgreSQL style formatting. It also sets the substyle to U.S., which uses additional United States-specific formatting.
booktown=# SET DATESTYLE TO Postgres,US;
SET VARIABLE
The next example sets the date and time formatting to ISO:
