Description
The SET command changes run-time configuration
parameters. Many of the run-time parameters listed in the
Administrator's Guide can be changed on-the-fly
with SET. (But some require superuser privileges
to change, and others cannot be changed after server or session start.)
Note that SET only affects the value used by the
current session.
If SET or SET SESSION is issued
within a transaction that is later aborted, the effects of the
SET command disappear when the transaction is rolled
back. (This behavior represents a change from
PostgreSQL versions prior to 7.3, where
the effects of SET would not roll back after a later
error.) Once the surrounding transaction is committed, the effects
will persist until the end of the session, unless overridden by another
SET.
The effects of SET LOCAL last only till the end of
the current transaction, whether committed or not. A special case is
SET followed by SET LOCAL within
a single transaction: the SET LOCAL value will be
seen until the end of the transaction, but afterwards (if the transaction
is committed) the SET value will take effect.
Even with autocommit set to off, SET
does not start a new transaction block. See the
autocommit section of the Administrator's
Guide for details.
Here are additional details about a few of the parameters that can be set:
- DATESTYLE
Choose the date/time representation style. Two separate
settings are involved: 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.
- PostgreSQL
Use traditional PostgreSQL format.
- German
Use dd.mm.yyyy for numeric date representations.
The following two options determine both a substyle of the
"SQL" and "PostgreSQL" 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.
SET DATESTYLE affects interpretation of
input and provides several standard output formats. For
applications needing different variations or tighter control
over input or output, consider using
the to_char family of
functions.
There are several now-deprecated means for setting the date style
in addition to the normal methods of setting it via SET or
a configuration-file entry:
| Setting the postmaster's PGDATESTYLE environment
variable. (This will be overridden by any of the other methods.)
|
| Running postmaster using the option -o -e to
set dates to the European convention.
(This overrides environment variables and configuration-file
entries.)
|
| Setting the client's 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. This is
equivalent to a manually issued SET DATESTYLE.
|
- NAMES
SET NAMES is an alias for SET CLIENT_ENCODING.
- 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 231-1.
The seed can also be set by invoking the
setseed SQL function:
SELECT setseed(value);
- SERVER_ENCODING
Shows the server-side multibyte encoding. (At present, this
parameter can be shown but not set, because the encoding is
determined at initdb time.)
- TIME ZONE
TIMEZONE Sets the default time zone for your session. Arguments can be
an SQL time interval constant, an integer or double precision
constant, or a string representing a time zone name recognized
by the host operating system.
Here are some typical values for time zone settings:
- 'PST8PDT'
Set the time zone for Berkeley, California.
- 'Portugal'
Set the time zone for Portugal.
- 'Europe/Rome'
Set the time zone for Italy.
- 7
Set the time zone to 7 hours offset west from GMT (equivalent
to PDT).
- INTERVAL '08:00' HOUR TO MINUTE
Set the time zone to 8 hours offset west from GMT (equivalent
to PST).
- LOCAL
DEFAULT Set the time zone to your local time zone (the one that
your operating system defaults to).
The available time zone names depend on your operating
system. For example, on Linux
/usr/share/zoneinfo contains the database
of time zones; the names of the files in that directory can be
used as parameters to this command.
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.
Use SHOW to show the
current setting of a parameter.
Examples
Set the style of date to traditional
PostgreSQL with European conventions:
SET DATESTYLE TO PostgreSQL,European;
Set the time zone for Berkeley, California, using quotes to
preserve the uppercase spelling of the time zone name (note
that the date style is PostgreSQL for this
example):
SET TIME ZONE 'PST8PDT';
SELECT CURRENT_TIMESTAMP AS today;
today
------------------------------------
Tue Feb 26 07:32:21.42834 2002 PST
Set the time zone for Italy (note the required single quotes to handle
the special characters):
SET TIME ZONE 'Europe/Rome';
SELECT CURRENT_TIMESTAMP AS today;
today
-------------------------------
2002-10-08 05:39:35.008271+02
