1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>SET</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="sql-selectinto.html" title="SELECT INTO" /><link rel="next" href="sql-set-constraints.html" title="SET CONSTRAINTS" /></head><body id="docContent" class="container-fluid col-10"><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">SET</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-selectinto.html" title="SELECT INTO">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-set-constraints.html" title="SET CONSTRAINTS">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-SET"><div class="titlepage"></div><a id="id-1.9.3.173.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">SET</span></h2><p>SET — change a run-time parameter</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
SET [ SESSION | LOCAL ] <em class="replaceable"><code>configuration_parameter</code></em> { TO | = } { <em class="replaceable"><code>value</code></em> | '<em class="replaceable"><code>value</code></em>' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { <em class="replaceable"><code>timezone</code></em> | LOCAL | DEFAULT }
</pre></div><div class="refsect1" id="id-1.9.3.173.5"><h2>Description</h2><p>
The <code class="command">SET</code> command changes run-time configuration
parameters. Many of the run-time parameters listed in
<a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a> can be changed on-the-fly with
<code class="command">SET</code>.
(But some require superuser privileges to change, and others cannot
be changed after server or session start.)
<code class="command">SET</code> only affects the value used by the current
session.
</p><p>
If <code class="command">SET</code> (or equivalently <code class="command">SET SESSION</code>)
is issued within a transaction that is later aborted, the effects of the
<code class="command">SET</code> command disappear when the transaction is rolled
back. Once the surrounding transaction is committed, the effects
will persist until the end of the session, unless overridden by another
<code class="command">SET</code>.
</p><p>
The effects of <code class="command">SET LOCAL</code> last only till the end of
the current transaction, whether committed or not. A special case is
<code class="command">SET</code> followed by <code class="command">SET LOCAL</code> within
a single transaction: the <code class="command">SET LOCAL</code> value will be
seen until the end of the transaction, but afterwards (if the transaction
is committed) the <code class="command">SET</code> value will take effect.
</p><p>
The effects of <code class="command">SET</code> or <code class="command">SET LOCAL</code> are
also canceled by rolling back to a savepoint that is earlier than the
command.
</p><p>
If <code class="command">SET LOCAL</code> is used within a function that has a
<code class="literal">SET</code> option for the same variable (see
<a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>),
the effects of the <code class="command">SET LOCAL</code> command disappear at
function exit; that is, the value in effect when the function was called is
restored anyway. This allows <code class="command">SET LOCAL</code> to be used for
dynamic or repeated changes of a parameter within a function, while still
having the convenience of using the <code class="literal">SET</code> option to save and
restore the caller's value. However, a regular <code class="command">SET</code> command
overrides any surrounding function's <code class="literal">SET</code> option; its effects
will persist unless rolled back.
</p><div class="note"><h3 class="title">Note</h3><p>
In <span class="productname">PostgreSQL</span> versions 8.0 through 8.2,
the effects of a <code class="command">SET LOCAL</code> would be canceled by
releasing an earlier savepoint, or by successful exit from a
<span class="application">PL/pgSQL</span> exception block. This behavior
has been changed because it was deemed unintuitive.
</p></div></div><div class="refsect1" id="id-1.9.3.173.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">SESSION</code></span></dt><dd><p>
Specifies that the command takes effect for the current session.
(This is the default if neither <code class="literal">SESSION</code> nor
<code class="literal">LOCAL</code> appears.)
</p></dd><dt><span class="term"><code class="literal">LOCAL</code></span></dt><dd><p>
Specifies that the command takes effect for only the current
transaction. After <code class="command">COMMIT</code> or <code class="command">ROLLBACK</code>,
the session-level setting takes effect again. Issuing this
outside of a transaction block emits a warning and otherwise has
no effect.
</p></dd><dt><span class="term"><em class="replaceable"><code>configuration_parameter</code></em></span></dt><dd><p>
Name of a settable run-time parameter. Available parameters are
documented in <a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a> and below.
</p></dd><dt><span class="term"><em class="replaceable"><code>value</code></em></span></dt><dd><p>
New value of parameter. Values can be specified as string
constants, identifiers, numbers, or comma-separated lists of
these, as appropriate for the particular parameter.
<code class="literal">DEFAULT</code> can be written to specify
resetting the parameter to its default value (that is, whatever
value it would have had if no <code class="command">SET</code> had been executed
in the current session).
</p></dd></dl></div><p>
Besides the configuration parameters documented in <a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a>, there are a few that can only be
adjusted using the <code class="command">SET</code> command or that have a
special syntax:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">SCHEMA</code></span></dt><dd><p><code class="literal">SET SCHEMA '<em class="replaceable"><code>value</code></em>'</code> is an alias for
<code class="literal">SET search_path TO <em class="replaceable"><code>value</code></em></code>. Only one
schema can be specified using this syntax.
</p></dd><dt><span class="term"><code class="literal">NAMES</code></span></dt><dd><p><code class="literal">SET NAMES <em class="replaceable"><code>value</code></em></code> is an alias for
<code class="literal">SET client_encoding TO <em class="replaceable"><code>value</code></em></code>.
</p></dd><dt><span class="term"><code class="literal">SEED</code></span></dt><dd><p>
Sets the internal seed for the random number generator (the
function <code class="function">random</code>). Allowed values are
floating-point numbers between -1 and 1, which are then
multiplied by 2<sup>31</sup>-1.
</p><p>
The seed can also be set by invoking the function
<code class="function">setseed</code>:
</p><pre class="programlisting">
SELECT setseed(<em class="replaceable"><code>value</code></em>);
</pre></dd><dt><span class="term"><code class="literal">TIME ZONE</code></span></dt><dd><p><code class="literal">SET TIME ZONE <em class="replaceable"><code>value</code></em></code> is an alias
for <code class="literal">SET timezone TO <em class="replaceable"><code>value</code></em></code>. The
syntax <code class="literal">SET TIME ZONE</code> allows special syntax
for the time zone specification. Here are examples of valid
values:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">'PST8PDT'</code></span></dt><dd><p>
The time zone for Berkeley, California.
</p></dd><dt><span class="term"><code class="literal">'Europe/Rome'</code></span></dt><dd><p>
The time zone for Italy.
</p></dd><dt><span class="term"><code class="literal">-7</code></span></dt><dd><p>
The time zone 7 hours west from UTC (equivalent
to PDT). Positive values are east from UTC.
</p></dd><dt><span class="term"><code class="literal">INTERVAL '-08:00' HOUR TO MINUTE</code></span></dt><dd><p>
The time zone 8 hours west from UTC (equivalent
to PST).
</p></dd><dt><span class="term"><code class="literal">LOCAL</code><br /></span><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p>
Set the time zone to your local time zone (that is, the
server's default value of <code class="varname">timezone</code>).
</p></dd></dl></div><p>
</p><p>
Timezone settings given as numbers or intervals are internally
translated to POSIX timezone syntax. For example, after
<code class="literal">SET TIME ZONE -7</code>, <code class="command">SHOW TIME ZONE</code> would
report <code class="literal"><-07>+07</code>.
</p><p>
See <a class="xref" href="datatype-datetime.html#DATATYPE-TIMEZONES" title="8.5.3. Time Zones">Section 8.5.3</a> for more information
about time zones.
</p></dd></dl></div><p>
</p></div><div class="refsect1" id="id-1.9.3.173.7"><h2>Notes</h2><p>
The function <code class="function">set_config</code> provides equivalent
functionality; see <a class="xref" href="functions-admin.html#FUNCTIONS-ADMIN-SET" title="9.27.1. Configuration Settings Functions">Section 9.27.1</a>.
Also, it is possible to UPDATE the
<a class="link" href="view-pg-settings.html" title="51.85. pg_settings"><code class="structname">pg_settings</code></a>
system view to perform the equivalent of <code class="command">SET</code>.
</p></div><div class="refsect1" id="id-1.9.3.173.8"><h2>Examples</h2><p>
Set the schema search path:
</p><pre class="programlisting">
SET search_path TO my_schema, public;
</pre><p>
</p><p>
Set the style of date to traditional
<span class="productname">POSTGRES</span> with <span class="quote">“<span class="quote">day before month</span>”</span>
input convention:
</p><pre class="screen">
SET datestyle TO postgres, dmy;
</pre><p>
</p><p>
Set the time zone for Berkeley, California:
</p><pre class="screen">
SET TIME ZONE 'PST8PDT';
</pre><p>
</p><p>
Set the time zone for Italy:
</p><pre class="screen">
SET TIME ZONE 'Europe/Rome';
</pre></div><div class="refsect1" id="id-1.9.3.173.9"><h2>Compatibility</h2><p>
<code class="literal">SET TIME ZONE</code> extends syntax defined in the SQL
standard. The standard allows only numeric time zone offsets while
<span class="productname">PostgreSQL</span> allows more flexible
time-zone specifications. All other <code class="literal">SET</code>
features are <span class="productname">PostgreSQL</span> extensions.
</p></div><div class="refsect1" id="id-1.9.3.173.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-reset.html" title="RESET"><span class="refentrytitle">RESET</span></a>, <a class="xref" href="sql-show.html" title="SHOW"><span class="refentrytitle">SHOW</span></a></span></div></div><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-selectinto.html" title="SELECT INTO">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-set-constraints.html" title="SET CONSTRAINTS">Next</a></td></tr><tr><td width="40%" align="left" valign="top">SELECT INTO </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> SET CONSTRAINTS</td></tr></table></div></body></html>
|
