summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/sql-set.html
blob: 240d45f54cd64da49d816d26f0acdfb404fb2e8d (plain)
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
<?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 Vsnapshot" /><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 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 15.6 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 /></div><div class="refentry" id="SQL-SET"><div class="titlepage"></div><a id="id-1.9.3.174.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>value</code></em> | '<em class="replaceable"><code>value</code></em>' | LOCAL | DEFAULT }
</pre></div><div class="refsect1" id="id-1.9.3.174.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 20. Server Configuration">Chapter 20</a> can be changed on-the-fly with
   <code class="command">SET</code>.
   (Some parameters can only be changed by superusers and users who
   have been granted <code class="literal">SET</code> privilege on that parameter.
   There are also parameters that 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.174.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 20. Server Configuration">Chapter 20</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 20. Server Configuration">Chapter 20</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 inclusive.
      </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">&lt;-07&gt;+07</code>.
      </p><p>
       Time zone abbreviations are not supported by <code class="command">SET</code>;
       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.174.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="54.24. 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.174.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.174.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.174.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 class="navfooter"><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 15.6 Documentation">Home</a></td><td width="40%" align="right" valign="top"> SET CONSTRAINTS</td></tr></table></div></body></html>