SET variable { TO | = } { 'value' | DEFAULT }
SET TIME ZONE { 'timezone' | LOCAL };
Settable global parameter.
New value of parameter.
The possible variables and allowed values are:
use ISO 8601-style dates and times
use Oracle/Ingres-style dates and times
use traditional Postgres format
use dd/mm/yyyy for numeric date representations.
use mm/dd/yyyy for numeric date representations.
use dd.mm.yyyy for numeric date representations.
same as 'NonEuropean'
restores the default values ('US,Postgres')
Date format initialization my be done by:
| Setting PGDATESTYLE environment variable. |
| Running postmaster using -oe parameter to set dates to the 'European' convention. Note that this affects only the 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_ISO_DATES
USE_SQL_DATES
USE_GERMAN_DATES
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:
set the timezone for California
set time zone for Portugal.
set time zone for Italy.
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).
A frontend which uses libpq may be initialized by setting the PGTZ environment variable.
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.
There are also several internal or optimization parameters which can be specified by the SET command:
Sets the default cost of a heap scan for use by the optimizer.
Set the cost of a heap scan to the specified floating point value.
Sets the cost of a heap scan to the default value.
The frontend may be initialized by setting the PGCOSTHEAP environment variable.
Sets the default cost of an index scan for use by the optimizer.
Set the cost of an index scan to the specified floating point value.
Sets the cost of an index scan to the default value.
The frontend may be initialized by setting the PGCOSTINDEX environment variable.
Sets the threshold for using the genetic optimizer algorithm.
enables the genetic optimizer algorithm for statements with 8 or more tables.
Takes an integer argument to enable the genetic optimizer algorithm for statements with # or more tables in the query.
disables the genetic optimizer algorithm.
Equivalent to specifying SET GEQO='on'
This algorithm is on by default, which used GEQO for statements of eight or more tables. (See the chapter on GEQO in the Programmer's Guide for more information).
The frontend may be initialized by setting PGGEQO environment variable.
Determines whether right-hand plan evaluation is allowed:
enables right-hand evaluation of plans.
disables right-hand evaluation of plans.
Equivalent to specifying SET R_PLANS='off'.
It may be useful when joining big relations with small ones. This algorithm is off by default. It's not used by GEQO anyway.
The frontend may be initialized by setting the PGRPLANS environment variable.
Key Set Query Optimizer forces the query optimizer to optimize repetative OR clauses such as generated by MicroSoft Access:
enables this optimization.
disables this optimization.
Equivalent to specifying SET KSQO='off'.
It may be useful when joining big relations with small ones. This algorithm is off by default. It's not used by GEQO anyway.
The frontend may be initialized by setting the PGRPLANS environment variable.
Sets the number of rows returned by a query.
Maximum number of rows to return for a query. The default is to allow an unlimited number of rows.
Sets the maximum number of rows returned by a query to #.
Sets the maximum number of rows returned by a query to be unlimited.
By default, there is no limit to the number of rows returned by a query.
Message returned if successfully.
If the command fails to set variable.
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. A 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.
The SET variable statement is a Postgres language extension.
Refer to SHOW and RESET to display or reset the current values.
--Set the style of date to ISO: -- SET DATESTYLE TO 'ISO';
--Set GEQO to default: -- SET GEQO = DEFAULT;
--Turn on right-hand evaluation of plans: -- SET R_PLANS TO 'on';
--set the timezone for Berkeley, California: SET TIME ZONE 'PST8PDT'; SELECT CURRENT_TIMESTAMP AS today; today ---------------------- 1998-03-31 07:41:21-08
--set the timezone for Italy: SET TIME ZONE 'Europe/Rome'; SELECT CURRENT_TIMESTAMP AS today; today ---------------------- 1998-03-31 17:41:31+02
There is no SET variable in SQL92. 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 }