Вся предоставленная на этом сервере информация собрана нами из разных источников. Если Вам кажется, что публикация каких-то документов нарушает чьи-либо авторские права, сообщите нам об этом.
SET variable { TO | = } { value | 'value' | DEFAULT }
SET CONSTRAINTS {ALL | constraintlist} mode
SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }
Inputs
variable
Settable global 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.
The possible variables and allowed values are:
CLIENT_ENCODING | NAMES
Sets the multi-byte client encoding. Parameters are:
value
Sets the multi-byte client encoding to
value.
The specified encoding must be supported by the backend.
This option is only available if MULTIBYTE support was enabled
during the configure step of building Postgres.
DATESTYLE
Set the date/time representation style. Affects the output format,
and in some cases it can affect the interpretation of input.
ISO
use ISO 8601-style dates and times
SQL
use Oracle/Ingres-style dates and times
Postgres
use traditional Postgres format
European
use dd/mm/yyyy for numeric date representations.
NonEuropean
use mm/dd/yyyy for numeric date representations.
German
use dd.mm.yyyy for numeric date representations.
US
same as NonEuropean
DEFAULT
restores the default values (ISO)
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 startup.
Running postmaster using the option -o -e to set
dates to the European convention.
Note that this affects only some combinations of date styles; for example
the ISO style is not affected by this parameter.
Changing variables in
src/backend/utils/init/globals.c.
The variables in globals.c which can be changed are:
bool EuroDates = false | true
int DateStyle = USE_ISO_DATES | USE_POSTGRES_DATES | USE_SQL_DATES | USE_GERMAN_DATES
SEED
Sets the internal seed for the random number generator.
value
The value for the seed to be used by the
random catalog function. Significant
values are floating point numbers between 0 and 1, which
are then multiplied by RAND_MAX. 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);
This option is only available if MULTIBYTE support was enabled
during the configure step of building Postgres.
SERVER_ENCODING
Sets the multi-byte server encoding to:
value
The identifying value for the server encoding.
This option is only available if MULTIBYTE support was enabled
during the configure step of building Postgres.
CONSTRAINTS
SET CONSTRAINTS affects the behavior of constraint evaluation
in the current transaction.
SET CONSTRAINTS, specified
in SQL3, has these allowed parameters:
constraintlist
Comma separated list of deferrable constraint names.
mode
The constraint mode. Allowed values are
DEFERRED and IMMEDIATE.
In IMMEDIATE mode, foreign key constraints
are checked at the end of each query.
In DEFERRED mode, foreign key constraints
marked as DEFERRABLE are checked only at
transaction commit or until its mode is explicitly set to
IMMEDIATE.
This is actually only done for foreign key
constraints, so it does not apply to UNIQUE or other
constraints.
TIME ZONE, TIMEZONE
The possible values for timezone depends on your operating
system. For example on Linux /usr/lib/zoneinfo contains the
database of timezones.
Here are some valid values for timezone:
PST8PDT
set the timezone for California
Portugal
set time zone for Portugal.
'Europe/Rome'
set time zone for Italy.
DEFAULT
set time zone to your local timezone
(value of the TZ environment variable).
If an invalid time zone is specified, the time zone
becomes GMT (on most systems anyway).
The second syntax shown above, allows one to set the timezone
with a syntax similar to SQL92 SET TIME ZONE.
The LOCAL keyword is just an alternate form
of DEFAULT for SQL92 compatibility.
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 startup.
TRANSACTION ISOLATION LEVEL
Sets the isolation level for the current transaction.
READ COMMITTED
The current transaction queries read only rows committed
before a query began. READ COMMITTED is the default.
Note: SQL92 standard requires
SERIALIZABLE to be the default isolation level.
SERIALIZABLE
The current transaction queries read only rows committed
before first DML statement
(SELECT/INSERT/DELETE/UPDATE/FETCH/COPY_TO)
was executed in this transaction.
There are also several internal or optimization
parameters which can be specified
by the SET command:
PG_OPTIONS
Sets various backend parameters.
RANDOM_PAGE_COST
Sets the optimizer's estimate of the cost of a nonsequentially
fetched disk page. This is measured as a multiple of the cost
of a sequential page fetch.
float8
Set the cost of a random page access
to the specified floating-point value.
CPU_TUPLE_COST
Sets the optimizer's estimate of the cost of processing each
tuple during a query. This is measured as a fraction of the cost
of a sequential page fetch.
float8
Set the cost of per-tuple CPU processing
to the specified floating-point value.
CPU_INDEX_TUPLE_COST
Sets the optimizer's estimate of the cost of processing each
index tuple during an index scan. This is measured as a fraction
of the cost of a sequential page fetch.
float8
Set the cost of per-index-tuple CPU processing
to the specified floating-point value.
CPU_OPERATOR_COST
Sets the optimizer's estimate of the cost of processing each
operator in a WHERE clause. This is measured as a fraction
of the cost of a sequential page fetch.
float8
Set the cost of per-operator CPU processing
to the specified floating-point value.
EFFECTIVE_CACHE_SIZE
Sets the optimizer's assumption about the effective size of the
disk cache (that is, the portion of the kernel's disk cache that
will be used for Postgres data files). This is measured in disk
pages, which are normally 8Kb apiece.
float8
Set the assumed cache size
to the specified floating-point value.
ENABLE_SEQSCAN
Enables or disables the planner's use of sequential scan plan types.
(It's not possible to suppress sequential scans entirely, but turning
this variable OFF discourages the planner from using one if there is
any other method available.)
ON
enables use of sequential scans (default setting).
OFF
disables use of sequential scans.
ENABLE_INDEXSCAN
Enables or disables the planner's use of index scan plan types.
ON
enables use of index scans (default setting).
OFF
disables use of index scans.
ENABLE_TIDSCAN
Enables or disables the planner's use of TID scan plan types.
ON
enables use of TID scans (default setting).
OFF
disables use of TID scans.
ENABLE_SORT
Enables or disables the planner's use of explicit sort steps.
(It's not possible to suppress explicit sorts entirely, but turning
this variable OFF discourages the planner from using one if there is
any other method available.)
ON
enables use of sorts (default setting).
OFF
disables use of sorts.
ENABLE_NESTLOOP
Enables or disables the planner's use of nested-loop join plans.
(It's not possible to suppress nested-loop joins entirely, but turning
this variable OFF discourages the planner from using one if there is
any other method available.)
ON
enables use of nested-loop joins (default setting).
OFF
disables use of nested-loop joins.
ENABLE_MERGEJOIN
Enables or disables the planner's use of mergejoin plans.
ON
enables use of merge joins (default setting).
OFF
disables use of merge joins.
ENABLE_HASHJOIN
Enables or disables the planner's use of hashjoin plans.
ON
enables use of hash joins (default setting).
OFF
disables use of hash joins.
GEQO
Sets the threshold for using the genetic optimizer algorithm.
ON
enables the genetic optimizer algorithm
for statements with 11 or more tables.
(This is also the DEFAULT setting.)
ON=#
Takes an integer argument to enable the genetic optimizer algorithm
for statements with #
or more tables in the query.
OFF
disables the genetic optimizer algorithm.
See the chapter on GEQO in the Programmer's Guide
for more information about query optimization.
If the PGGEQO environment variable is set in the frontend
environment of a client based on libpq, libpq will automatically
set GEQO to the value of PGGEQO during connection startup.
KSQO
Key Set Query Optimizer causes the query
planner to convert queries whose WHERE clause contains many
OR'ed AND clauses (such as "WHERE (a=1 AND b=2) OR (a=2 AND b=3) ...")
into a UNION query. This method can be faster than the default
implementation, but it doesn't necessarily give exactly the same
results, since UNION implicitly adds a SELECT DISTINCT clause to
eliminate identical output rows. KSQO is commonly used when
working with products like MicroSoft
Access, which tend to generate queries of this form.
ON
enables this optimization.
OFF
disables this optimization (default setting).
DEFAULT
Equivalent to specifying SET KSQO=OFF.
The KSQO algorithm used to be absolutely essential for queries
with many OR'ed AND clauses, but in Postgres 7.0 and later
the standard planner handles these queries fairly successfully.
MAX_EXPR_DEPTH
Sets the maximum expression nesting depth that the parser will
accept. The default value is high enough for any normal query,
but you can raise it if you need to. (But if you raise it too high,
you run the risk of backend crashes due to stack overflow.)
integer
Maximum depth.
Outputs
SET VARIABLE
Message returned if successful.
WARN: Bad value for variable (value)
If the command fails to set the specified variable.
Description
SET will modify configuration parameters for variable during
a session.
Current values can be obtained using SHOW, and values
can be restored to the defaults using RESET.
Parameters and values are case-insensitive. Note that the value
field is always specified as a string, so is enclosed in
single-quotes.
SET TIME ZONE changes the session's
default time zone offset.
An SQL-session always begins with an initial default time zone
offset.
The SET TIME ZONE statement is used to change the default
time zone offset for the current SQL session.
Notes
The SET variable
statement is a Postgres language extension.
Refer to SHOW and RESET to
display or reset the current values.
Usage
Set the style of date to ISO (no quotes on the argument is required):
SET DATESTYLE TO ISO;
Enable GEQO for queries with 4 or more tables (note the use of
single quotes to handle the equal sign inside the value argument):
SET GEQO = 'ON=4';
Set GEQO to default:
SET GEQO = DEFAULT;
Set the timezone for Berkeley, California, using double quotes to
preserve the uppercase
attributes of the time zone specifier:
SET TIME ZONE "PST8PDT";
SELECT CURRENT_TIMESTAMP AS today;
today
------------------------
1998-03-31 07:41:21-08
Set the timezone 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
There is no general
SET variable
in SQL92 (with the exception of
SET TRANSACTION ISOLATION LEVEL).
The SQL92 syntax for SET TIME ZONE
is slightly different,
allowing only a single integer value for time zone specification:
SET TIME ZONE { interval_value_expression | LOCAL }
