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 com
plex 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 sep
arate settings are made: the default date/time out
put 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 acci
dent.
Postgres
Use traditional Postgres format.
German Use dd.mm.yyyy for numeric date representa
tions.
``SQL'' and ``Postgres'' output formats and the preferred
interpretation of ambiguous date input.
European
Use dd/mm/yyyy for numeric date representa
tions.
NonEuropean
US Use mm/dd/yyyy for numeric date representa
tions.
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 PGDAT
ESTYLE 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 gener
ator.
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.
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 auto
matically set TIMEZONE to the value of PGTZ during connec
tion start-up.
An extended list of other run-time parameters can be found
in the Administrator's Guide.
Use SHOW [show(l)] to show the current setting of a param
eters.
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
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 Euro
pean 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.
Man(1) output converted with
man2html