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
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>SET</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-selectinto.html" title="SELECT INTO">
<link rel="next" href="sql-set-constraints.html" title="SET CONSTRAINTS">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-set"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>SET — change a run-time parameter</p>
</div>
<a name="id787073"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">SET [ SESSION | LOCAL ] <em class="replaceable"><code>name</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" lang="en">
<a name="id787117"></a><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 href="runtime-config.html" title="Chapter17.Server Configuration">Chapter17, <i>Server Configuration</i></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 <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. (This behavior represents a change from
<span class="productname">PostgreSQL</span> versions prior to 7.3, where
the effects of <code class="command">SET</code> would not roll back after a later
error.) 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>
</div>
<div class="refsect1" lang="en">
<a name="id787240"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<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. Note that
<code class="command">SET LOCAL</code> will appear to have no effect if it is
executed outside a <code class="command">BEGIN</code> block, since the
transaction will end immediately.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt>
<dd><p> Name of a settable run-time parameter. Available parameters are
documented in <a href="runtime-config.html" title="Chapter17.Server Configuration">Chapter17, <i>Server Configuration</i></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. <code class="literal">DEFAULT</code> can be used to specify
resetting the parameter to its default value.
</p></dd>
</dl></div>
<p> Besides the configuration parameters documented in <a href="runtime-config.html" title="Chapter17.Server Configuration">Chapter17, <i>Server Configuration</i></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>
<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 0 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>
<p>
</p>
</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>
<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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="term"><code xmlns="" class="literal">LOCAL</code></span><br xmlns="http://www.w3.org/TR/xhtml1/transitional"></br><span class="term"><code class="literal">DEFAULT</code></span>
</dt>
<dd><p> Set the time zone to your local time zone (the one that
the server's operating system defaults to).
</p></dd>
</dl></div>
<p>
See <a href="datatype-datetime.html" title="8.5.Date/Time Types">Section8.5, “Date/Time Types”</a> for more information
about time zones. Also, <a href="datetime-appendix.html" title="AppendixB.Date/Time Support">AppendixB, <i>Date/Time Support</i></a>
has a list of the recognized names for time zones.
</p>
</dd>
</dl></div>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id787580"></a><h2>Notes</h2>
<p> The function <code class="function">set_config</code> provides equivalent
functionality. See <a href="functions-admin.html" title="9.20.System Administration Functions">Section9.20, “System Administration Functions”</a>.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id787601"></a><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">day before month</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>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id787656"></a><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" lang="en">
<a name="id787692"></a><h2>See Also</h2>
<span class="simplelist"><a href="sql-reset.html">RESET</a>, <a href="sql-show.html">SHOW</a></span>
</div>
</div></body>
</html>
|
