1SET(7) PostgreSQL 9.2.24 Documentation SET(7)
2
6 SET - change a run-time parameter
7
9 SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value | 'value' | DEFAULT }
10 SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
11
13 The SET command changes run-time configuration parameters. Many of the
14 run-time parameters listed in Chapter 18, Server Configuration, in the
15 documentation can be changed on-the-fly with SET. (But some require
16 superuser privileges to change, and others cannot be changed after
17 server or session start.) SET only affects the value used by the
18 current session.
19 If SET (or equivalently SET SESSION) is issued within a transaction
21 that is later aborted, the effects of the SET command disappear when
22 the transaction is rolled back. Once the surrounding transaction is
23 committed, the effects will persist until the end of the session,
24 unless overridden by another SET.
25 The effects of SET LOCAL last only till the end of the current
27 transaction, whether committed or not. A special case is SET followed
28 by SET LOCAL within a single transaction: the SET LOCAL value will be
29 seen until the end of the transaction, but afterwards (if the
30 transaction is committed) the SET value will take effect.
31 The effects of SET or SET LOCAL are also canceled by rolling back to a
33 savepoint that is earlier than the command.
34 If SET LOCAL is used within a function that has a SET option for the
36 same variable (see CREATE FUNCTION (CREATE_FUNCTION(7))), the effects
37 of the SET LOCAL command disappear at function exit; that is, the value
38 in effect when the function was called is restored anyway. This allows
39 SET LOCAL to be used for dynamic or repeated changes of a parameter
40 within a function, while still having the convenience of using the SET
41 option to save and restore the caller's value. However, a regular SET
42 command overrides any surrounding function's SET option; its effects
43 will persist unless rolled back.
44 Note
46 In PostgreSQL versions 8.0 through 8.2, the effects of a SET LOCAL
47 would be canceled by releasing an earlier savepoint, or by
48 successful exit from a PL/pgSQL exception block. This behavior has
49 been changed because it was deemed unintuitive.
50
52 SESSION
53 Specifies that the command takes effect for the current session.
54 (This is the default if neither SESSION nor LOCAL appears.)
55 LOCAL
57 Specifies that the command takes effect for only the current
58 transaction. After COMMIT or ROLLBACK, the session-level setting
59 takes effect again. Note that SET LOCAL will appear to have no
60 effect if it is executed outside a BEGIN block, since the
61 transaction will end immediately.
62 configuration_parameter
64 Name of a settable run-time parameter. Available parameters are
65 documented in Chapter 18, Server Configuration, in the
66 documentation and below.
67 value
69 New value of parameter. Values can be specified as string
70 constants, identifiers, numbers, or comma-separated lists of these,
71 as appropriate for the particular parameter. DEFAULT can be
72 written to specify resetting the parameter to its default value
73 (that is, whatever value it would have had if no SET had been
74 executed in the current session).
75 Besides the configuration parameters documented in Chapter 18, Server
77 Configuration, in the documentation, there are a few that can only be
78 adjusted using the SET command or that have a special syntax:
79 SCHEMA
81 SET SCHEMA 'value' is an alias for SET search_path TO value. Only
82 one schema can be specified using this syntax.
83 NAMES
85 SET NAMES value is an alias for SET client_encoding TO value.
86 SEED
88 Sets the internal seed for the random number generator (the
89 function random). Allowed values are floating-point numbers between
90 -1 and 1, which are then multiplied by 231-1.
91 The seed can also be set by invoking the function setseed:
93 SELECT setseed(value);
95 TIME ZONE
97 SET TIME ZONE value is an alias for SET timezone TO value. The
98 syntax SET TIME ZONE allows special syntax for the time zone
99 specification. Here are examples of valid values:
100 'PST8PDT'
102 The time zone for Berkeley, California.
103 'Europe/Rome'
105 The time zone for Italy.
106 -7
108 The time zone 7 hours west from UTC (equivalent to PDT).
109 Positive values are east from UTC.
110 INTERVAL '-08:00' HOUR TO MINUTE
112 The time zone 8 hours west from UTC (equivalent to PST).
113 LOCAL, DEFAULT
115 Set the time zone to your local time zone (that is, the
116 server's default value of timezone).
117 See Section 8.5.3, “Time Zones”, in the documentation for more
119 information about time zones.
120
122 The function set_config provides equivalent functionality; see Section
123 9.26, “System Administration Functions”, in the documentation. Also, it
124 is possible to UPDATE the pg_settings system view to perform the
125 equivalent of SET.
126
128 Set the schema search path:
129 SET search_path TO my_schema, public;
131 Set the style of date to traditional POSTGRES with “day before month”
133 input convention:
134 SET datestyle TO postgres, dmy;
136 Set the time zone for Berkeley, California:
138 SET TIME ZONE 'PST8PDT';
140 Set the time zone for Italy:
142 SET TIME ZONE 'Europe/Rome';
144
146 SET TIME ZONE extends syntax defined in the SQL standard. The standard
147 allows only numeric time zone offsets while PostgreSQL allows more
148 flexible time-zone specifications. All other SET features are
149 PostgreSQL extensions.
150
152 RESET(7), SHOW(7)
153PostgreSQL 9.2.24 2017-11-06 SET(7)