1SET(7) PostgreSQL 15.4 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 { value | 'value' | LOCAL | DEFAULT }
11
13 The SET command changes run-time configuration parameters. Many of the
14 run-time parameters listed in Chapter 20 can be changed on-the-fly with
15 SET. (Some parameters can only be changed by superusers and users who
16 have been granted SET privilege on that parameter. There are also
17 parameters that cannot be changed after server or session start.) SET
18 only affects the value used by the 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. Issuing this outside of a transaction block
60 emits a warning and otherwise has no effect.
61 configuration_parameter
63 Name of a settable run-time parameter. Available parameters are
64 documented in Chapter 20 and below.
65 value
67 New value of parameter. Values can be specified as string
68 constants, identifiers, numbers, or comma-separated lists of these,
69 as appropriate for the particular parameter. DEFAULT can be
70 written to specify resetting the parameter to its default value
71 (that is, whatever value it would have had if no SET had been
72 executed in the current session).
73 Besides the configuration parameters documented in Chapter 20, there
75 are a few that can only be adjusted using the SET command or that have
76 a special syntax:
77 SCHEMA
79 SET SCHEMA 'value' is an alias for SET search_path TO value. Only
80 one schema can be specified using this syntax.
81 NAMES
83 SET NAMES value is an alias for SET client_encoding TO value.
84 SEED
86 Sets the internal seed for the random number generator (the
87 function random). Allowed values are floating-point numbers between
88 -1 and 1 inclusive.
89 The seed can also be set by invoking the function setseed:
91 SELECT setseed(value);
93 TIME ZONE
95 SET TIME ZONE 'value' is an alias for SET timezone TO 'value'. The
96 syntax SET TIME ZONE allows special syntax for the time zone
97 specification. Here are examples of valid values:
98 'PST8PDT'
100 The time zone for Berkeley, California.
101 'Europe/Rome'
103 The time zone for Italy.
104 -7
106 The time zone 7 hours west from UTC (equivalent to PDT).
107 Positive values are east from UTC.
108 INTERVAL '-08:00' HOUR TO MINUTE
110 The time zone 8 hours west from UTC (equivalent to PST).
111 LOCAL
113 DEFAULT
114 Set the time zone to your local time zone (that is, the
115 server's default value of timezone).
116 Timezone settings given as numbers or intervals are internally
118 translated to POSIX timezone syntax. For example, after SET TIME
119 ZONE -7, SHOW TIME ZONE would report <-07>+07.
120 Time zone abbreviations are not supported by SET; see Section 8.5.3
122 for more information about time zones.
123
125 The function set_config provides equivalent functionality; see
126 Section 9.27.1. Also, it is possible to UPDATE the pg_settings system
127 view to perform the equivalent of SET.
128
130 Set the schema search path:
131 SET search_path TO my_schema, public;
133 Set the style of date to traditional POSTGRES with “day before month”
135 input convention:
136 SET datestyle TO postgres, dmy;
138 Set the time zone for Berkeley, California:
140 SET TIME ZONE 'PST8PDT';
142 Set the time zone for Italy:
144 SET TIME ZONE 'Europe/Rome';
146
148 SET TIME ZONE extends syntax defined in the SQL standard. The standard
149 allows only numeric time zone offsets while PostgreSQL allows more
150 flexible time-zone specifications. All other SET features are
151 PostgreSQL extensions.
152
154 RESET(7), SHOW(7)
155PostgreSQL 15.4 2023 SET(7)