set.sgml

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/set.sgml,v 1.58 2002/02/26 15:59:08 tgl Exp $
PostgreSQL documentation
-->

<refentry id="SQL-SET">
 <refmeta>
  <refentrytitle id="SQL-SET-TITLE">SET</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>SET</refname>
  <refpurpose>change a run-time parameter</refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <synopsis>
SET <replaceable class="PARAMETER">variable</replaceable> { TO | = } { <replaceable class="PARAMETER">value</replaceable> | '<replaceable class="PARAMETER">value</replaceable>' | DEFAULT }
SET TIME ZONE { '<replaceable class="PARAMETER">timezone</replaceable>' | LOCAL | DEFAULT }
  </synopsis>

  <refsect2 id="R2-SQL-SET-1">
   <title>Inputs</title>
   <para>
    <variablelist>
     <varlistentry>
      <term><replaceable class="PARAMETER">variable</replaceable></term>
      <listitem>
       <para>
	A settable run-time parameter.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="PARAMETER">value</replaceable></term>
      <listitem>
       <para>
	New value of parameter.  <option>DEFAULT</option> can be
	used to specify resetting the parameter to its default
	value. Lists of strings are allowed, but more complex
	constructs may need to be single or double quoted.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

 </refsynopsisdiv>
 
 <refsect1 id="R1-SQL-SET-1">
  <title>Description</title>
  <para>
   The <command>SET</command> command changes run-time configuration
   parameters. The following parameters can be altered:

   <variablelist>
    <varlistentry>
     <term>CLIENT_ENCODING</term>
     <term>NAMES</term>
     <listitem>
      <para>
       Sets the multibyte client encoding. The specified encoding
       must be supported by the backend.
      </para>

      <para>
       This option is only available if
       <productname>PostgreSQL</productname> is build with multibyte
       support.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term>DATESTYLE</term>
     <listitem>
      <para>
       Choose the date/time representation style. Two separate
       settings are made: the default date/time output and the
       interpretation of ambiguous input.
      </para>

      <para>
       The following are date/time output styles:

       <variablelist>
	<varlistentry>
	 <term>ISO</term>
	 <listitem>
	  <para>
	   Use ISO 8601-style dates and times (<literal>YYYY-MM-DD
	   HH:MM:SS</literal>). This is the default.
          </para>
	 </listitem>
	</varlistentry>

        <varlistentry>
	 <term>SQL</term>
	 <listitem>
	  <para>
	   Use Oracle/Ingres-style dates and times. Note that this
	   style has nothing to do with SQL (which mandates ISO 8601
	   style), the naming of this option is a historical accident.
	  </para>
	 </listitem>
	</varlistentry>

        <varlistentry>
	 <term>PostgreSQL</term>
	 <listitem>
	  <para>
	   Use traditional <productname>PostgreSQL</productname> format.
	  </para>
	 </listitem>
	</varlistentry>

        <varlistentry>
	 <term>German</term>
	 <listitem>
	  <para>
	   Use <literal>dd.mm.yyyy</literal> for numeric date representations.
	  </para>
	 </listitem>
	</varlistentry>
       </variablelist>
      </para>

      <para>
       The following two options determine both a substyle of the
       <quote>SQL</quote> and <quote>PostgreSQL</quote> output formats
       and the preferred interpretation of ambiguous date input.

       <variablelist>
	<varlistentry>
	 <term>European</term>
	 <listitem>
	  <para>
	   Use <literal>dd/mm/yyyy</literal> for numeric date representations.
	  </para>
	 </listitem>
	</varlistentry>

        <varlistentry>
	 <term>NonEuropean</term>
	 <term>US</term>
	 <listitem>
	  <para>
	   Use <literal>mm/dd/yyyy</literal> for numeric date representations.
	  </para>
	 </listitem>
	</varlistentry>
       </variablelist>
      </para>

      <para>
       A value for <command>SET DATESTYLE</command> can be one from
       the first list (output styles), or one from the second list
       (substyles), or one from each separated by a comma.
      </para>

      <para>
       Date format initialization may be done by:
       <simplelist>
	<member>
	 Setting the <envar>PGDATESTYLE</envar> environment variable.
	 If PGDATESTYLE is set in the frontend environment of a client
	 based on libpq, libpq will automatically set DATESTYLE to the
	 value of PGDATESTYLE during connection start-up.
	</member>
	<member>
	 Running postmaster using the option <option>-o -e</option> to
	 set dates to the <literal>European</literal> convention.
	</member>
       </simplelist>
      </para>
      <para>
       The <option>DateStyle</option> option is really only intended
       for porting applications. To format your date/time values to
       choice, use the <function>to_char</function> family of
       functions.
      </para>
     </listitem>
    </varlistentry>

     <varlistentry>
      <term>SEED</term>
      <listitem>
       <para>
	Sets the internal seed for the random number generator.
	
	<variablelist>
	 <varlistentry>
	  <term><replaceable class="parameter">value</replaceable></term>
	  <listitem>
	   <para>
	    The value for the seed to be used by the
	    <function>random</function> function. Allowed
	    values are floating-point numbers between 0 and 1, which
	    are then multiplied by 2<superscript>31</>-1. This product will
	    silently overflow if a number outside the range is used.
	   </para>

	   <para>
	    The seed can also be set by invoking the
	    <function>setseed</function> SQL function:

	    <programlisting>
SELECT setseed(<replaceable>value</replaceable>);
	    </programlisting>
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>

      </listitem>
     </varlistentry>

     <varlistentry>
      <term>SERVER_ENCODING</term>
      <listitem>
       <para>
	Sets the multibyte server encoding.
       </para>

       <para>
	This option is only available if
	<productname>PostgreSQL</productname> was built with multibyte
	support.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term>TIME ZONE</term>
      <term>TIMEZONE</term>
      <listitem>
      <para>
       Sets the default time zone for your session. Arguments can be
       an SQL time interval constant, an integer or double precision
       constant, or a string representing a time zone supported by
       the host operating system.
      </para>

       <para>
	The possible values for time zone depends on your operating
	system. For example, on Linux
	<filename>/usr/share/zoneinfo</filename> contains the database
	of time zones.
       </para>
       <para>
	Here are some valid values for time zone:

       <variablelist>
	 <varlistentry>
	  <term>'PST8PDT'</term>
	  <listitem>
	   <para>
	    Set the time zone for California.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>'Portugal'</term>
	  <listitem>
	   <para>
	    Set the time zone for Portugal.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>'Europe/Rome'</term>
	  <listitem>
	   <para>
	    Set the time zone for Italy.
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>7</term>
	  <listitem>
	   <para>
	    Set the time zone to 7 hours offset west from GMT (equivalent
	   to PDT).
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>INTERVAL '08:00' HOUR TO MINUTE</term>
	  <listitem>
	   <para>
	    Set the time zone to 8 hours offset west from GMT (equivalent
	   to PST).
	   </para>
	  </listitem>
	 </varlistentry>
	 <varlistentry>
	  <term>LOCAL</term>
          <term>DEFAULT</term>
	  <listitem>
	   <para>
	    Set the time zone to your local time zone (the one that
	    your operating system defaults to).
	   </para>
	  </listitem>
	 </varlistentry>
	</variablelist>
       </para>
       <para>
	If an invalid time zone is specified, the time zone
	becomes GMT (on most systems anyway).
       </para>
       <para>
        If the <envar>PGTZ</envar> environment variable is set in the frontend
	environment of a client based on libpq, libpq will automatically
	set TIMEZONE to the value of <envar>PGTZ</envar> during connection start-up.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>
   </para>

  <para>
   An extended list of other run-time parameters can be found in the
   <citetitle>Administrator's Guide</citetitle>.
  </para>

  <para>
   Use <xref linkend="SQL-SHOW" endterm="SQL-SHOW-title"> to show the
   current setting of a parameters.
  </para>
  
 </refsect1>

 <refsect1>
  <title>Diagnostics</title>
  <para>
    
   <variablelist>
    <varlistentry>
     <term><computeroutput>SET VARIABLE</computeroutput></term>
     <listitem>
      <para>
       Message returned if successful.
      </para>
     </listitem>
    </varlistentry>
     
    <varlistentry>
     <term><computeroutput>ERROR:  not a valid option name: <replaceable>name</replaceable></computeroutput></term>
     <listitem>
      <para>
       The parameter you tried to set does not exist.
      </para>
     </listitem>
    </varlistentry>
     
    <varlistentry>
     <term><computeroutput>ERROR:  permission denied</computeroutput></term>
     <listitem>
      <para>
       You must be a superuser to have access to certain settings.
      </para>
     </listitem>
    </varlistentry>
     
    <varlistentry>
     <term><computeroutput>ERROR:  <replaceable>name</replaceable> can only be set at start-up</computeroutput></term>
     <listitem>
      <para>
       Some parameters are fixed once the server is started.
      </para>
     </listitem>
    </varlistentry>
     
   </variablelist>
  </para>
 </refsect1>

 
 <refsect1>
  <title>Examples</title>
  <para>
   Set the style of date to traditional
   <productname>PostgreSQL</productname> with European conventions:
<screen>
SET DATESTYLE TO PostgreSQL,European;
</screen>

   Set the time zone for Berkeley, California, using double quotes to
   preserve the uppercase attributes of the time zone specifier (note
   that the date/time format is ISO here):

<screen> 
SET TIME ZONE 'PST8PDT';
SELECT CURRENT_TIMESTAMP AS today;
   
         today
------------------------
 1998-03-31 07:41:21-08
</screen>

   Set the time zone for Italy (note the required single or double quotes to handle 
   the special characters):

<screen> 
SET TIME ZONE 'Europe/Rome';
SELECT CURRENT_TIMESTAMP AS today;
   
         today
------------------------
 1998-03-31 17:41:31+02
</screen>
  </para>
 </refsect1>

 <refsect1 id="R1-SQL-SET-3">
  <title>Compatibility</title>

  <refsect2 id="R2-SQL-SET-4">
   <title>
    SQL92
   </title>

   <para>
    The second syntax shown above (<literal>SET TIME ZONE</literal>)
    attempts to mimic <acronym>SQL92</acronym>. However, SQL allows
    only numeric time zone offsets. All other parameter settings as
    well as the first syntax shown above are a
    <productname>PostgreSQL</productname> extension.
   </para>
  </refsect2>
 </refsect1>
</refentry>

<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->