Set runtime variables.
SET variable { TO | = } { value | 'value' | DEFAULT } SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
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
Pacific Standard/Daylight Savings Time (GMT offset by 8 hours).
EST5EDT
Eastern Standard/Daylight Savings Time (GMT offset by 5 hours).
NZST13NZDT
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.
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.
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).
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:
booktown=# SET DATESTYLE TO ISO;
SET VARIABLE
3.144.37.12