SET SQL - Language Statements SET Set run-time parameters for session 1999-07-20 SET variable { TO | = } { 'value' | DEFAULT } SET TIME ZONE { 'timezone' | LOCAL | DEFAULT } SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE } 1998-09-24 variable Settable global parameter. value New value of parameter. DEFAULT can be used to specify resetting the parameter to its default value. 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 ('US,Postgres') 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 SERVER_ENCODING Sets the multi-byte server encoding value Sets the multi-byte server encoding. This option is only available if MULTIBYTE support was enabled during the configure step of building Postgres. 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. 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: 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. 1998-09-24 SET VARIABLE Message returned if successfully. WARN: Bad value for variable (value) If the command fails to set the specified variable. 1998-09-24 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. 1998-09-24 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'; Enable GEQO for queries with 4 or more tables: SET GEQO ON=4; Set GEQO to default: SET GEQO = DEFAULT; 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 1998-09-24 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 }