2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/set.sgml,v 1.47 2000/08/07 00:51:18 tgl Exp $
6 <refentry id="SQL-SET">
8 <refentrytitle id="SQL-SET-TITLE">SET</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
12 <refname>SET</refname>
13 <refpurpose>Set run-time parameters</refpurpose>
17 SET <replaceable class="PARAMETER">variable</replaceable> { TO | = } { <replaceable class="PARAMETER">value</replaceable> | '<replaceable class="PARAMETER">value</replaceable>' | DEFAULT }
18 SET TIME ZONE { '<replaceable class="PARAMETER">timezone</replaceable>' | LOCAL | DEFAULT }
21 <refsect2 id="R2-SQL-SET-1">
26 <term><replaceable class="PARAMETER">variable</replaceable></term>
29 A settable run-time parameter.
34 <term><replaceable class="PARAMETER">value</replaceable></term>
37 New value of parameter. <option>DEFAULT</option> can be
38 used to specify resetting the parameter to its default
39 value. Lists of strings are allowed, but more complex
40 constructs may need to be single or double quoted.
50 <refsect1 id="R1-SQL-SET-1">
51 <title>Description</title>
53 The <command>SET</command> command changes run-time configuration
54 parameters. The following parameters can be altered:
58 <term>CLIENT_ENCODING</term>
62 Sets the multi-byte client encoding. The specified encoding
63 must be supported by the backend.
67 This option is only available if
68 <productname>Postgres</productname> is build with multibyte
75 <term>DATESTYLE</term>
78 Choose the date/time representation style. Two separate
79 settings are made: the default date/time output and the
80 interpretation of ambiguous input.
84 The following are date/time output styles:
91 Use ISO 8601-style dates and times (<literal>YYYY-MM-DD
92 HH:MM:SS</literal>). This is the default.
101 Use Oracle/Ingres-style dates and times. Note that this
102 style has nothing to do with SQL (which mandates ISO 8601
103 style), the naming of this option is a historical accident.
109 <term>Postgres</term>
112 Use traditional <productname>Postgres</productname> format.
121 Use <literal>dd.mm.yyyy</literal> for numeric date representations.
129 The following two options determine both a substyle of the
130 <quote>SQL</quote> and <quote>Postgres</quote> output formats
131 and the preferred interpretation of ambiguous date input.
135 <term>European</term>
138 Use <literal>dd/mm/yyyy</literal> for numeric date representations.
144 <term>NonEuropean</term>
148 Use <literal>mm/dd/yyyy</literal> for numeric date representations.
156 A value for <command>SET DATESTYLE</command> can be one from
157 the first list (output styles), or one from the second list
158 (substyles), or one from each separated by a comma.
162 Date format initialization may be done by:
165 Setting the <envar>PGDATESTYLE</envar> environment variable.
166 If PGDATESTYLE is set in the frontend environment of a client
167 based on libpq, libpq will automatically set DATESTYLE to the
168 value of PGDATESTYLE during connection startup.
171 Running postmaster using the option <option>-o -e</option> to
172 set dates to the <literal>European</literal> convention.
177 The <option>DateStyle</option> option is really only intended
178 for porting applications. To format your date/time values to
179 choice, use the <function>to_char</function> family of
189 Sets the internal seed for the random number generator.
193 <term><replaceable class="parameter">value</replaceable></term>
196 The value for the seed to be used by the
197 <function>random</function> function. Allowed
198 values are floating point numbers between 0 and 1, which
199 are then multiplied by 2^31-1. This product will
200 silently overflow if a number outside the range is used.
204 The seed can also be set by invoking the
205 <function>setseed</function> SQL function:
208 SELECT setseed(<replaceable>value</replaceable>);
220 <term>SERVER_ENCODING</term>
223 Sets the multi-byte server encoding.
227 This option is only available if
228 <productname>Postgres</productname> was built with multibyte
235 <term>TIME ZONE</term>
236 <term>TIMEZONE</term>
239 The possible values for timezone depends on your operating
240 system. For example, on Linux
241 <filename>/usr/share/zoneinfo</filename> contains the database
245 Here are some valid values for timezone:
252 Set the time zone for California.
257 <term>Portugal</term>
260 Set time zone for Portugal.
265 <term>'Europe/Rome'</term>
268 Set time zone for Italy.
277 Set the time zone to your local time zone (the one that
278 your operating system defaults to).
285 If an invalid time zone is specified, the time zone
286 becomes GMT (on most systems anyway).
289 If the PGTZ environment variable is set in the frontend
290 environment of a client based on libpq, libpq will automatically
291 set TIMEZONE to the value of PGTZ during connection startup.
300 An extended list of other run-time parameters can be found in the
301 <citetitle>Administrator's Guide</citetitle>.
305 Use <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title"> to show the
306 current setting of a parameters.
312 <title>Diagnostics</title>
317 <term><computeroutput>SET VARIABLE</computeroutput></term>
320 Message returned if successful.
326 <term><computeroutput>ERROR: not a valid option name: <replaceable>name</replaceable></computeroutput></term>
329 The parameter you tried to set does not exist.
335 <term><computeroutput>ERROR: permission denied</computeroutput></term>
338 You must be a superuser to have access to certain settings.
344 <term><computeroutput>ERROR: <replaceable>name</replaceable> can only be set at startup</computeroutput></term>
347 Some parameters are fixed once the server is started.
358 <title>Examples</title>
360 Set the style of date to traditional Postgres with European conventions:
362 SET DATESTYLE TO Postgres,European;
365 Set the timezone for Berkeley, California, using double quotes to
366 preserve the uppercase attributes of the time zone specifier (note
367 that the date/time format is ISO here):
370 SET TIME ZONE "PST8PDT";
371 SELECT CURRENT_TIMESTAMP AS today;
374 ------------------------
375 1998-03-31 07:41:21-08
378 Set the timezone for Italy (note the required single or double quotes to handle
379 the special characters):
382 SET TIME ZONE 'Europe/Rome';
383 SELECT CURRENT_TIMESTAMP AS today;
386 ------------------------
387 1998-03-31 17:41:31+02
392 <refsect1 id="R1-SQL-SET-3">
393 <title>Compatibility</title>
395 <refsect2 id="R2-SQL-SET-4">
401 The second syntax shown above (<literal>SET TIME ZONE</literal>)
402 attempts to mimic <acronym>SQL92</acronym>. However, SQL allows
403 only numeric time zone offsets. All other parameter settings as
404 well as the first syntax shown above are a
405 <productname>Postgres</productname> extension.
411 <!-- Keep this comment at the end of the file
416 sgml-minimize-attributes:nil
417 sgml-always-quote-attributes:t
420 sgml-parent-document:nil
421 sgml-default-dtd-file:"../reference.ced"
422 sgml-exposed-tags:nil
423 sgml-local-catalogs:("/usr/lib/sgml/catalog")
424 sgml-local-ecat-files:nil