<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title> Practical PostgreSQL</title>
<meta name="keywords" content="postgresql, support, recovery, design, software, hosting, expterise, programming, postgres, pgsql, replication, mysql, firebird, database, sql, free, open source, software" />
<meta name="description" content="Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. " />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="../css/layout.css" />
<link rel="stylesheet" type="text/css" href="../css/cmd4.css" />
<script language="javascript" type="text/javascript"><!--
greymenuhomewideLit = new Image();
greymenuhomewideLit.src = "/images/greymenu_homewide_lit.gif";
greymenuaboutLit = new Image();
greymenuaboutLit.src = "/images/greymenu_about_lit.gif";
greymenuproductsLit = new Image();
greymenuproductsLit.src = "/images/greymenu_products_lit.gif";
greymenuservicesLit = new Image();
greymenuservicesLit.src = "/images/greymenu_services_lit.gif";
greymenusupportLit = new Image();
greymenusupportLit.src = "/images/greymenu_support_lit.gif";
greymenucommunityLit = new Image();
greymenucommunityLit.src = "/images/greymenu_community_lit.gif";
//--></script>
</head>
<body>
<div align="center">
<div id="widecontainer">
<div id="widetop">
<div class="throwleft" style="width: 300px; text-align: left;"><a href="http://www.commandprompt.com/"><img src="../images/cmd4logo_wide.gif" border="0" alt="Command Prompt, Inc." /></a></div>
<div class="throwright" style="width: 400px; text-align: right;">
<div id="topright">
<form action="index.html" name="cmdform" method="post">
<ul>
<li><input type="text" name="search" value="" /></li>
<li><input type="submit" name="action" value="Search Book" /></li>
</ul>
</form>
</div>
</div>
</div>
<ul id="greymenu_wide">
<li><a href="http://www.commandprompt.com/home/"><img onmouseover="this.src = greymenuhomewideLit.src;" onmouseout="this.src = '/images/greymenu_homewide.gif';" src="../images/greymenu_homewide.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/about/"><img onmouseover="this.src = greymenuaboutLit.src;" onmouseout="this.src = '/images/greymenu_about.gif';" src="../images/greymenu_about.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/products/"><img onmouseover="this.src = greymenuproductsLit.src;" onmouseout="this.src = '/images/greymenu_products.gif';" src="../images/greymenu_products.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/services/"><img onmouseover="this.src = greymenuservicesLit.src;" onmouseout="this.src = '/images/greymenu_services.gif';" src="../images/greymenu_services.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/support/"><img onmouseover="this.src = greymenusupportLit.src;" onmouseout="this.src = '/images/greymenu_support.gif';" src="../images/greymenu_support.gif" border="0" height="22" title="" alt="" /></a></li><li><img src="../images/greymenu_divider.gif" border="0" width="2" height="22" alt="|" /></li><li><a href="http://www.commandprompt.com/community/"><img onmouseover="this.src = greymenucommunityLit.src;" onmouseout="this.src = '/images/greymenu_community.gif';" src="../images/greymenu_community.gif" border="0" height="22" title="" alt="" /></a></li> </ul>
<div style="padding-left: 16px; width: 96%;">
<HTML
><HEAD
><TITLE
>SET</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.72
"><LINK
REL="HOME"
TITLE="Practical PostgreSQL"
href="book1"><LINK
REL="UP"
TITLE="PostgreSQL Command Reference"
href="c22759"><LINK
REL="PREVIOUS"
TITLE="SELECT INTO"
href="r28390"><LINK
REL="NEXT"
TITLE="SET CONSTRAINTS"
href="r28639"></HEAD
><BODY
CLASS="REFENTRY"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>Practical PostgreSQL</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
href="r28390"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
href="r28639"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="SQL-SET"
>SET</A
></H1
><DIV
CLASS="REFNAMEDIV"
><A
NAME="AEN28468"
></A
><H2
>Name</H2
>SET -- Set runtime variables.</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN28471"
></A
><H2
>Synopsis</H2
><PRE
CLASS="SYNOPSIS"
>SET <TT
CLASS="REPLACEABLE"
><I
>variable</I
></TT
> { TO | = } { <TT
CLASS="REPLACEABLE"
><I
>value</I
></TT
> | '<TT
CLASS="REPLACEABLE"
><I
>value</I
></TT
>' | DEFAULT }
SET TIME ZONE { '<TT
CLASS="REPLACEABLE"
><I
>timezone</I
></TT
>' | LOCAL | DEFAULT }</PRE
><DIV
CLASS="REFSECT2"
><A
NAME="R2-SQL-SET-1"
></A
><H3
>Parameters</H3
><P
> <P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>variable</I
></TT
></DT
><DD
><P
>The name of the runtime variable you are setting.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>value</I
></TT
></DT
><DD
><P
>A new value for the specified variable. Use <TT
CLASS="LITERAL"
>DEFAULT</TT
> to reset the variable to its default value.</P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>timezone</I
></TT
></DT
><DD
><P
>The time zone of the client. The following are a few valid time zone values:
<P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>PST8PDT</TT
></DT
><DD
><P
>The Pacific Standard/Daylight Savings Time (GMT offset by 8 hours).</P
></DD
><DT
><TT
CLASS="LITERAL"
>EST5EDT</TT
></DT
><DD
><P
>The Eastern Standard/Daylight Savings Time (GMT offset by 5 hours).</P
></DD
><DT
><TT
CLASS="LITERAL"
>NZST13NZDT</TT
></DT
><DD
><P
>The standard/daylight savings time zone in New Zealand (GMT offset by 13 hours).</P
></DD
></DL
></DIV
>
</P
></DD
><DT
><TT
CLASS="LITERAL"
>LOCAL</TT
></DT
><DD
><P
>The clause to set the time zone to the local system's configured time zone.</P
></DD
><DT
><TT
CLASS="LITERAL"
>DEFAULT</TT
></DT
><DD
><P
>The clause to set a variable value, or a time zone value, to its default.</P
></DD
></DL
></DIV
>
</P
></DIV
><DIV
CLASS="REFSECT2"
><A
NAME="R2-SQL-SET-2"
></A
><H3
>Results</H3
><P
> <P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>SET VARIABLE</TT
></DT
><DD
><P
>The message returned when a variable is successfully set.</P
></DD
><DT
><TT
CLASS="LITERAL"
>ERROR: not a valid option name:(</TT
><TT
CLASS="REPLACEABLE"
><I
>name</I
></TT
><TT
CLASS="LITERAL"
>)</TT
></DT
><DD
><P
>The error returned if you try to set a variable that doesn't exist. </P
></DD
><DT
><TT
CLASS="LITERAL"
>ERROR: permission denied</TT
></DT
><DD
><P
>The error returned if you do not have adequate permissions to alter the specified variable.</P
></DD
><DT
><TT
CLASS="LITERAL"
>ERROR: name can only be set at start-up</TT
></DT
><DD
><P
>The error returned if you attempt to set a variable that can only set set upon startup. </P
></DD
></DL
></DIV
>
</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="R1-SQL-SET-1"
></A
><H2
>Description</H2
><P
>Use the <TT
CLASS="LITERAL"
>SET</TT
> command to modify PostgreSQL runtime configuration variables. The following variable can be altered:
</P
><A
NAME="AEN28553"
></A
><BLOCKQUOTE
CLASS="BLOCKQUOTE"
><P
></P
></BLOCKQUOTE
><P
> <P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>CLIENT_ENCODING</TT
></DT
><DD
><P
>The multibyte client encoding scheme (if enabled in PostgreSQL).</P
></DD
><DT
><TT
CLASS="LITERAL"
>DATESTYLE</TT
></DT
><DD
><P
>This variable sets the date and time representation style. When setting this variable, you can choose one format from the normal output styles, one of the two substyles, or both an output style and a substyle. Initialize the format by manually changing the <TT
CLASS="LITERAL"
>PGDATESTYLE</TT
> environment variable. You can also initialize the format using <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> command options. For example, run <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
> using <SPAN
><I
CLASS="EMPHASIS"
>-o "-e"</I
></SPAN
> to set dates to the European format (see <A
href="c16573"
>Chapter 9</A
> for more about the options available to <SPAN
><I
CLASS="EMPHASIS"
>postmaster</I
></SPAN
>).</P
><P
>The following are valid date and time output styles:
</P
><A
NAME="AEN28574"
></A
><BLOCKQUOTE
CLASS="BLOCKQUOTE"
><P
></P
></BLOCKQUOTE
><P
> <P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>ISO</TT
></DT
><DD
><P
>The ISO-8601–style date and time formatting. Date and time are displayed as <TT
CLASS="REPLACEABLE"
><I
>YYYY-MM-DD HH:MM:SS</I
></TT
>. This is the default style.</P
></DD
><DT
><TT
CLASS="LITERAL"
>SQL</TT
></DT
><DD
><P
>The Oracle/Ingres–style date and time formatting. Despite this format's label, it is not the SQL default; SQL uses ISO–8601 style formatting. </P
></DD
><DT
><TT
CLASS="LITERAL"
>Postgres</TT
></DT
><DD
><P
>The traditional PostgreSQL date and time formatting.</P
></DD
><DT
><TT
CLASS="LITERAL"
>German</TT
></DT
><DD
><P
>The traditional German-style formatting. Numeric date representations are displayed as <TT
CLASS="REPLACEABLE"
><I
>DD.MM.YYYY</I
></TT
>.</P
></DD
><DT
><TT
CLASS="LITERAL"
>European</TT
></DT
><DD
><P
>The standard European-style formatting. This is a substyle of the <SPAN
><I
CLASS="EMPHASIS"
>SQL</I
></SPAN
> and <SPAN
><I
CLASS="EMPHASIS"
>PostgreSQL</I
></SPAN
> styles. Numeric date representations are displayed as <TT
CLASS="REPLACEABLE"
><I
>DD/MM/YYYY</I
></TT
>.</P
></DD
><DT
><TT
CLASS="LITERAL"
>NonEuropean, US</TT
></DT
><DD
><P
>The standard United States-style formatting. This is a substyle of the <SPAN
><I
CLASS="EMPHASIS"
>SQL</I
></SPAN
> and <SPAN
><I
CLASS="EMPHASIS"
>Postgres</I
></SPAN
> styles. Numeric date representations are displayed as <TT
CLASS="REPLACEABLE"
><I
>MM/DD/YYYY</I
></TT
>.</P
></DD
></DL
></DIV
>
</P
></DD
><DT
><TT
CLASS="LITERAL"
>SEED</TT
></DT
><DD
><P
>This variable sets the internal seed for the PostgreSQL random number generator, which is used by the
<TT
CLASS="LITERAL"
>random()</TT
> function. Allowed values are floating point numbers between 0 and 1. The number you supply
is then multiplied by 2^30.</P
><P
>Alternatively, you may set the seed by calling the SQL <TT
CLASS="LITERAL"
>setseed()</TT
> function, with a single argument of type <TT
CLASS="LITERAL"
>double precision</TT
>.</P
></DD
><DT
><TT
CLASS="LITERAL"
>SERVER_ENCODING</TT
></DT
><DD
><P
>The server's default multibyte encoding (if enabled in PostgreSQL).</P
></DD
></DL
></DIV
>
</P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="R1-SQL-SET-2"
></A
><H2
>Examples</H2
><P
>The following example sets the <TT
CLASS="LITERAL"
>DATESTYLE</TT
> variable to use traditional PostgreSQL style formatting. It also sets the substyle to U.S., which uses additional United States-specific formatting.</P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>SET DATESTYLE TO Postgres,US;</B
></TT
>
SET VARIABLE</PRE
><P
>The next example sets the date and time formatting to ISO:</P
><PRE
CLASS="SCREEN"
>booktown=# <TT
CLASS="USERINPUT"
><B
>SET DATESTYLE TO ISO;</B
></TT
>
SET VARIABLE</PRE
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
href="r28390"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="book1"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
href="r28639"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>SELECT INTO</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
href="c22759"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>SET CONSTRAINTS</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
</div>
<br />
<div id="thefineprint">
<a href="http://www.commandprompt.com/products/mammothpostgresql/"><img src="../images/powered_by_mammoth.gif" alt="Powered by Mammoth PostgreSQL" title="Powered by Mammoth PostgreSQL" border="0" /></a>
<br />
Copyright © 2000-2007 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.
</div>
</div>
</div>
</body>
</html>