FAQ.html 54.8 KB
Newer Older
1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
Bruce Momjian's avatar
Bruce Momjian committed
2
<HTML>
Bruce Momjian's avatar
Bruce Momjian committed
3 4
  <HEAD>
    <META name="generator" content="HTML Tidy, see www.w3.org">
Bruce Momjian's avatar
Bruce Momjian committed
5
    <META http-equiv="Content-Type" content="text/html; charset=US-ASCII">
Bruce Momjian's avatar
Bruce Momjian committed
6 7 8
    <TITLE>PostgreSQL FAQ</TITLE>
  </HEAD>

Bruce Momjian's avatar
Bruce Momjian committed
9 10
  <BODY bgcolor="#ffffff" text="#000000" link="#ff0000" vlink="#a00000"
  alink="#0000ff">
11
    <H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>
Bruce Momjian's avatar
Bruce Momjian committed
12

Bruce Momjian's avatar
Bruce Momjian committed
13
    <P>Last updated: Sat Jan 29 23:44:48 EST 2005</P>
Bruce Momjian's avatar
Bruce Momjian committed
14

Bruce Momjian's avatar
Bruce Momjian committed
15
    <P>Current maintainer: Bruce Momjian (<A href=
Bruce Momjian's avatar
Bruce Momjian committed
16
    "mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)
Bruce Momjian's avatar
Bruce Momjian committed
17 18
    </P>

19
    <P>The most recent version of this document can be viewed at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
20 21
    "http://www.postgresql.org/files/documentation/faqs/FAQ.html">
    http://www.postgresql.org/files/documentation/faqs/FAQ.html</A>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
22

Bruce Momjian's avatar
Bruce Momjian committed
23
    <P>Platform-specific questions are answered at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
24 25
    "http://www.postgresql.org/docs/faq/">
    http://www.postgresql.org/docs/faq/</A>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
26 27
    <HR>

Bruce Momjian's avatar
Bruce Momjian committed
28
    <H2 align="center">General Questions</H2>
29
    <A href="#1.1">1.1</A>) What is PostgreSQL? How is it pronounced?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
30
     <A href="#1.2">1.2</A>) What is the copyright on PostgreSQL?<BR>
31 32 33 34 35 36
     <A href="#1.3">1.3</A>) What platforms does PostgreSQL support?<BR>
     <A href="#1.4">1.4</A>) Where can I get PostgreSQL?<BR>
     <A href="#1.5">1.5</A>) Where can I get support?<BR>
     <A href="#1.6">1.6</A>) What is the latest release?<BR>
     <A href="#1.7">1.7</A>) What documentation is available?<BR>
     <A href="#1.8">1.8</A>) How do I find out about known bugs or
Bruce Momjian's avatar
Bruce Momjian committed
37
    missing features?<BR>
38 39 40 41
     <A href="#1.9">1.9</A>) How can I learn <SMALL>SQL</SMALL>?<BR>
     <A href="#1.10">1.10</A>) How do I join the development team?<BR>
     <A href="#1.11">1.11</A>) How do I submit a bug report?<BR>
     <A href="#1.12">1.12</A>) How does PostgreSQL compare to other
Bruce Momjian's avatar
Bruce Momjian committed
42
    <SMALL>DBMS</SMALL>s?<BR>
43
     <A href="#1.13">1.13</A>) How can I financially assist
Bruce Momjian's avatar
Bruce Momjian committed
44
    PostgreSQL?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
45 46
     

Bruce Momjian's avatar
Bruce Momjian committed
47
    <H2 align="center">User Client Questions</H2>
48
    <A href="#2.1">2.1</A>) What interfaces are available for
Bruce Momjian's avatar
Bruce Momjian committed
49 50 51 52 53
    PostgreSQL?<BR>
     <A href="#2.2">2.2</A>) What tools are available for using
    PostgreSQL with Web pages?<BR>
     <A href="#2.3">2.3</A>) Does PostgreSQL have a graphical user
    interface?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
54 55
     

Bruce Momjian's avatar
Bruce Momjian committed
56
    <H2 align="center">Administrative Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
57 58
    <A href="#3.1">3.1</A>) How do I install PostgreSQL somewhere other
    than <I>/usr/local/pgsql</I>?<BR>
59
     <A href="#3.2">3.2</A>) How do I control connections from other
Bruce Momjian's avatar
Bruce Momjian committed
60
    hosts?<BR>
61
     <A href="#3.3">3.3</A>) How do I tune the database engine for
Bruce Momjian's avatar
Bruce Momjian committed
62
    better performance?<BR>
63 64
     <A href="#3.4">3.4</A>) What debugging features are available?<BR>
     <A href="#3.5">3.5</A>) Why do I get <I>"Sorry, too many
Bruce Momjian's avatar
Bruce Momjian committed
65
    clients"</I> when trying to connect?<BR>
66
     <A href="#3.6">3.6</A>) What is in the  <I>pgsql_tmp</I>
67
    directory?<BR>
68
     <A href="#3.7">3.7</A>) Why do I need to do a dump and restore
69
    to upgrade PostgreSQL releases?<BR>
70
     <A href="#3.8">3.8</A>) What computer hardware should I use?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
71

Bruce Momjian's avatar
Bruce Momjian committed
72

Bruce Momjian's avatar
Bruce Momjian committed
73
    <H2 align="center">Operational Questions</H2>
74
     <A href="#4.1">4.1</A>) How do I <SMALL>SELECT</SMALL> only the
75
    first few rows of a query?  A random row?<BR>
76
     <A href="#4.2">4.2</A>) How do I find out what tables, indexes,
77 78
    databases, and users are defined?  How do I see the queries used
    by <I>psql</I> to display them?<BR>
79
     <A href="#4.3">4.3</A>) How do you remove a column from a
Bruce Momjian's avatar
Bruce Momjian committed
80
    table, or change its data type?<BR>
81
     <A href="#4.4">4.4</A>) What is the maximum size for a row, a
Bruce Momjian's avatar
Bruce Momjian committed
82
    table, and a database?<BR>
83
     <A href="#4.5">4.5</A>) How much database disk space is required
Bruce Momjian's avatar
Bruce Momjian committed
84
    to store data from a typical text file?<BR>
85
     <A href="#4.6">4.6</A>) Why are my queries slow?  Why don't they
86
    use my indexes?<BR>
87
     <A href="#4.7">4.7</A>) How do I see how the query optimizer is
Bruce Momjian's avatar
Bruce Momjian committed
88
    evaluating my query?<BR>
89
     <A href="#4.8">4.8</A>) How do I perform regular expression
Bruce Momjian's avatar
Bruce Momjian committed
90 91
    searches and case-insensitive regular expression searches? How do I
    use an index for case-insensitive searches?<BR>
92
     <A href="#4.9">4.9</A>) In a query, how do I detect if a field
Bruce Momjian's avatar
Bruce Momjian committed
93
    is <SMALL>NULL</SMALL>?<BR>
94
     <A href="#4.10">4.10</A>) What is the difference between the
Bruce Momjian's avatar
Bruce Momjian committed
95
    various character types?<BR>
96
     <A href="#4.11.0">4.11.0</A>) How do I create a
Bruce Momjian's avatar
Bruce Momjian committed
97
    serial/auto-incrementing field?<BR>
98
     <A href="#4.11.1">4.11.1</A>) How do I get the value of a
Bruce Momjian's avatar
Bruce Momjian committed
99
    <SMALL>SERIAL</SMALL> insert?<BR>
100
     <A href="#4.11.2">4.11.2</A>) Doesn't <I>currval()</I>
101
    lead to a race condition with other users?<BR>
102
     <A href="#4.11.3">4.11.3</A>) Why aren't my sequence numbers
Bruce Momjian's avatar
Bruce Momjian committed
103 104
    reused on transaction abort? Why are there gaps in the numbering of
    my sequence/SERIAL column?<BR>
105
     <A href="#4.12">4.12</A>) What is an <SMALL>OID</SMALL>? What is a
Bruce Momjian's avatar
Bruce Momjian committed
106
    <SMALL>TID</SMALL>?<BR>
107
     <A href="#4.13">4.13</A>) What is the meaning of some of the terms
Bruce Momjian's avatar
Bruce Momjian committed
108
    used in PostgreSQL?<BR>
109
     <A href="#4.14">4.14</A>) Why do I get the error <I>"ERROR: Memory
Bruce Momjian's avatar
Bruce Momjian committed
110
    exhausted in AllocSetAlloc()"</I>?<BR>
111
     <A href="#4.15">4.15</A>) How do I tell what PostgreSQL version I
Bruce Momjian's avatar
Bruce Momjian committed
112
    am running?<BR>
113
     <A href="#4.16">4.16</A>) Why does my large-object operations get
Bruce Momjian's avatar
Bruce Momjian committed
114
    <I>"invalid large obj descriptor"</I>?<BR>
115
     <A href="#4.17">4.17</A>) How do I create a column that will
Bruce Momjian's avatar
Bruce Momjian committed
116
    default to the current time?<BR>
117 118
     <A href="#4.18">4.18</A>) How do I perform an outer join?<BR>
     <A href="#4.19">4.19</A>) How do I perform queries using multiple
Bruce Momjian's avatar
Bruce Momjian committed
119
    databases?<BR>
120
     <A href="#4.20">4.20</A>) How do I return multiple rows or columns
121
    from a function?<BR>
122
     <A href="#4.21">4.21</A>) Why can't I reliably create/drop
123
    temporary tables in PL/PgSQL functions?<BR>
124
     <A href="#4.22">4.22</A>) What encryption options are available?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
125 126
     

Bruce Momjian's avatar
Bruce Momjian committed
127
    <H2 align="center">Extending PostgreSQL</H2>
Bruce Momjian's avatar
Bruce Momjian committed
128 129 130 131 132 133 134 135
    <A href="#5.1">5.1</A>) I wrote a user-defined function. When I run
    it in <I>psql</I>, why does it dump core?<BR>
     <A href="#5.2">5.2</A>) How can I contribute some nifty new types
    and functions to PostgreSQL?<BR>
     <A href="#5.3">5.3</A>) How do I write a C function to return a
    tuple?<BR>
     <A href="#5.4">5.4</A>) I have changed a source file. Why does the
    recompile not see the change?<BR>
Bruce Momjian's avatar
Bruce Momjian committed
136 137 138
     
    <HR>

Bruce Momjian's avatar
Bruce Momjian committed
139
    <H2 align="center">General Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
140

141
    <H4><A name="1.1">1.1</A>) What is PostgreSQL? How is it pronounced?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
142

143
    <P>PostgreSQL is pronounced <I>Post-Gres-Q-L</I>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
144

Bruce Momjian's avatar
Bruce Momjian committed
145
    <P>PostgreSQL is an enhancement of the POSTGRES database management
Bruce Momjian's avatar
Bruce Momjian committed
146 147
    system (and is still sometimes reffered to as simply "Postgres"), 
    a next-generation <SMALL>DBMS</SMALL> research prototype.
Bruce Momjian's avatar
Bruce Momjian committed
148 149 150 151 152
    While PostgreSQL retains the powerful data model and rich data
    types of POSTGRES, it replaces the PostQuel query language with an
    extended subset of <SMALL>SQL</SMALL>. PostgreSQL is free and the
    complete source is available.</P>

153
    <P>PostgreSQL development is performed by a team of
Bruce Momjian's avatar
Bruce Momjian committed
154 155 156
    developers who all subscribe to the PostgreSQL development mailing
    list. The current coordinator is Marc G. Fournier (<A href=
    "mailto:scrappy@PostgreSQL.org">scrappy@PostgreSQL.org</A>). (See
157
    section <a href="#1.6">1.6</a> on how to join). This team is now
158
    responsible for all development of PostgreSQL.  It is a community
159
    project and is not controlled by any company.  To get involved, see
Bruce Momjian's avatar
Bruce Momjian committed
160
    the developer's FAQ at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
161 162
    "http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html">
    http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html</A>
163
    </P>
Bruce Momjian's avatar
Bruce Momjian committed
164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179

    <P>The authors of PostgreSQL 1.01 were Andrew Yu and Jolly Chen.
    Many others have contributed to the porting, testing, debugging,
    and enhancement of the code. The original Postgres code, from which
    PostgreSQL is derived, was the effort of many graduate students,
    undergraduate students, and staff programmers working under the
    direction of Professor Michael Stonebraker at the University of
    California, Berkeley.</P>

    <P>The original name of the software at Berkeley was Postgres. When
    <SMALL>SQL</SMALL> functionality was added in 1995, its name was
    changed to Postgres95. The name was changed at the end of 1996 to
    PostgreSQL.</P>

    <H4><A name="1.2">1.2</A>) What is the copyright on
    PostgreSQL?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
180 181 182 183 184

    <P>PostgreSQL is subject to the following COPYRIGHT:</P>

    <P>PostgreSQL Data Base Management System</P>

185 186
    <P>Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
    Portions Copyright (c) 1994-6 Regents of the University of California</P>
Bruce Momjian's avatar
Bruce Momjian committed
187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206

    <P>Permission to use, copy, modify, and distribute this software
    and its documentation for any purpose, without fee, and without a
    written agreement is hereby granted, provided that the above
    copyright notice and this paragraph and the following two
    paragraphs appear in all copies.</P>

    <P>IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY
    PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL
    DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS
    SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF
    CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.</P>

    <P>THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY
    WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES
    OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE
    SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE
    UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE,
    SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.</P>

Bruce Momjian's avatar
Bruce Momjian committed
207 208 209
    <P>The above is the BSD license, the classic open-source license.
    It has no restrictions on how the source code may be used. We like
    it and have no intention of changing it.</P>
Bruce Momjian's avatar
Bruce Momjian committed
210

211
    <H4><A name="1.3">1.3</A>) What platforms does PostgreSQL support?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
212

Bruce Momjian's avatar
Bruce Momjian committed
213
    <P>In general, any modern Unix-compatible platform should be able to
Bruce Momjian's avatar
Bruce Momjian committed
214 215 216
    run PostgreSQL. The platforms that had received explicit testing at
    the time of release are listed in the installation
    instructions.</P>
Bruce Momjian's avatar
Bruce Momjian committed
217

218 219 220 221
    <P>Starting with version 8.0, PostgreSQL now runs natively on
    Microsoft Windows NT-based operating systems like Win2000, WinXP,
    and Win2003.  A prepackaged installer is available at <a href=
    "http://pgfoundry.org/projects/pginstaller">
222 223
    http://pgfoundry.org/projects/pginstaller</a>.  MSDOS-based versions
    of Windows (Win95, Win98, WinMe) can run PostgreSQL using Cygwin.
Bruce Momjian's avatar
Bruce Momjian committed
224

Bruce Momjian's avatar
Bruce Momjian committed
225 226 227
    <p>There is also a Novell Netware 6 port at
    <a href="http://forge.novell.com">http://forge.novell.com</a>,
    and an OS/2 (eComStation) version at <a href=
Bruce Momjian's avatar
Bruce Momjian committed
228 229
    "http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&amp;button=Search&amp;key=postgreSQL&amp;stype=all&amp;sort=type&amp;dir=%2F">
    http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&amp;button=Search&amp;key=postgreSQL&amp;stype=all&amp;sort=type&amp;dir=%2F</a>.</p>
Bruce Momjian's avatar
Bruce Momjian committed
230

231
    <H4><A name="1.4">1.4</A>) Where can I get PostgreSQL?</H4>
232

Bruce Momjian's avatar
Bruce Momjian committed
233 234 235
    <P>The primary anonymous ftp site for PostgreSQL is <A href=
    "ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A>.
    For mirror sites, see our main web site.</P>
236

237
    <H4><A name="1.5">1.5</A>) Where can I get support?</H4>
238

Bruce Momjian's avatar
Bruce Momjian committed
239 240 241 242 243
    <P>The main mailing list is: <A href=
    "mailto:pgsql-general@PostgreSQL.org">pgsql-general@PostgreSQL.org</A>.
    It is available for discussion of matters pertaining to PostgreSQL.
    To subscribe, send mail with the following lines in the body (not
    the subject line):</P>
Bruce Momjian's avatar
Bruce Momjian committed
244 245 246 247
<PRE>
    subscribe
    end
</PRE>
248

Bruce Momjian's avatar
Bruce Momjian committed
249 250
    <P>to <A href=
    "mailto:pgsql-general-request@PostgreSQL.org">pgsql-general-request@PostgreSQL.org</A>.</P>
251

Bruce Momjian's avatar
Bruce Momjian committed
252 253 254 255
    <P>There is also a digest list available. To subscribe to this
    list, send email to: <A href=
    "mailto:pgsql-general-digest-request@PostgreSQL.org">pgsql-general-digest-request@PostgreSQL.org</A>
    with a body of:</P>
Bruce Momjian's avatar
Bruce Momjian committed
256 257 258 259
<PRE>
    subscribe
    end
</PRE>
260

Bruce Momjian's avatar
Bruce Momjian committed
261 262
    Digests are sent out to members of this list whenever the main list
    has received around 30k of messages. 
263

Bruce Momjian's avatar
Bruce Momjian committed
264 265 266 267
    <P>The bugs mailing list is available. To subscribe to this list,
    send email to <A href=
    "mailto:pgsql-bugs-request@PostgreSQL.org">pgsql-bugs-request@PostgreSQL.org</A>
    with a body of:</P>
Bruce Momjian's avatar
Bruce Momjian committed
268 269 270 271
<PRE>
    subscribe
    end
</PRE>
272

Bruce Momjian's avatar
Bruce Momjian committed
273 274 275 276
    There is also a developers discussion mailing list available. To
    subscribe to this list, send email to <A href=
    "mailto:pgsql-hackers-request@PostgreSQL.org">pgsql-hackers-request@PostgreSQL.org</A>
    with a body of: 
Bruce Momjian's avatar
Bruce Momjian committed
277 278 279 280
<PRE>
    subscribe
    end
</PRE>
281

Bruce Momjian's avatar
Bruce Momjian committed
282 283
    <P>Additional mailing lists and information about PostgreSQL can be
    found via the PostgreSQL WWW home page at:</P>
284

Bruce Momjian's avatar
Bruce Momjian committed
285 286 287
    <BLOCKQUOTE>
      <A href="http://www.PostgreSQL.org">http://www.PostgreSQL.org</A>
    </BLOCKQUOTE>
288

Bruce Momjian's avatar
Bruce Momjian committed
289 290 291
    <P>The major IRC channel is <I>#postgresql</I> on Freenode
    (<I>irc.freenode.net</I>).  To connect you can use the Unix
    command <CODE>irc -c '#postgresql' "$USER" irc.freenode.net</CODE>
292
    or use any of the other popular IRC clients.  A Spanish one also exists
Bruce Momjian's avatar
Bruce Momjian committed
293 294
    on the same network, (<I>#postgresql-es</I>), and a French one,
    (<I>#postgresqlfr</I>).  There is also a PostgreSQL channel on EFNet.
295

Bruce Momjian's avatar
Bruce Momjian committed
296
    <P>A list of commercial support companies is available at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
297
    "http://techdocs.postgresql.org/companies.php">http://techdocs.postgresql.org/companies.php</A>.</P>
298

299
    <H4><A name="1.6">1.6</A>) What is the latest release?</H4>
300

301
    <P>The latest release of PostgreSQL is version 8.0.0.</P>
302

Bruce Momjian's avatar
Bruce Momjian committed
303
    <P>We plan to have major releases every six to eight months.</P>
304

305
    <H4><A name="1.7">1.7</A>) What documentation is available?</H4>
306

Bruce Momjian's avatar
Bruce Momjian committed
307 308
    <P>Several manuals, manual pages, and some small test examples are
    included in the distribution. See the <I>/doc</I> directory. You
Bruce Momjian's avatar
Bruce Momjian committed
309 310
    can also browse the manuals online at <A href=
    "http://www.PostgreSQL.org/docs">http://www.PostgreSQL.org/docs</A>.</P>
311

Bruce Momjian's avatar
Bruce Momjian committed
312 313 314 315
    <P>There are two PostgreSQL books available online at <A href=
    "http://www.PostgreSQL.org/docs/awbook.html">http://www.PostgreSQL.org/docs/awbook.html</A>
    and <A href=
    "http://www.commandprompt.com/ppbook/">http://www.commandprompt.com/ppbook/</A>.
Bruce Momjian's avatar
Bruce Momjian committed
316 317
    There is a list of PostgreSQL books available for purchase at <A
    href=
Bruce Momjian's avatar
Bruce Momjian committed
318
    "http://techdocs.postgresql.org/techdocs/bookreviews.php">http://techdocs.PostgreSQL.org/techdocs/bookreviews.php</A>.
Bruce Momjian's avatar
Bruce Momjian committed
319 320
    There is also a collection of PostgreSQL technical articles at <A
    href=
321
    "http://techdocs.PostgreSQL.org/">http://techdocs.PostgreSQL.org/</A>.</P>
322

Bruce Momjian's avatar
Bruce Momjian committed
323 324 325
    <P>The command line client program <I>psql</I> has some \d commands to show
    information about types, operators, functions, aggregates, etc. - use \? to 
    display the available commands.</P>
326

Bruce Momjian's avatar
Bruce Momjian committed
327
    <P>Our web site contains even more documentation.</P>
Bruce Momjian's avatar
Bruce Momjian committed
328

329
    <H4><A name="1.8">1.8</A>) How do I find out about known bugs or
Bruce Momjian's avatar
Bruce Momjian committed
330
    missing features?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
331

Bruce Momjian's avatar
Bruce Momjian committed
332 333 334
    <P>PostgreSQL supports an extended subset of <SMALL>SQL</SMALL>-92.
    See our <A href="http://developer.PostgreSQL.org/todo.php">TODO</A>
    list for known bugs, missing features, and future plans.</P>
335

336
    <H4><A name="1.9">1.9</A>) How can I learn
Bruce Momjian's avatar
Bruce Momjian committed
337
    <SMALL>SQL</SMALL>?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
338

Bruce Momjian's avatar
Bruce Momjian committed
339 340
    <P>The PostgreSQL book at <A href=
    "http://www.PostgreSQL.org/docs/awbook.html">http://www.PostgreSQL.org/docs/awbook.html</A>
Bruce Momjian's avatar
Bruce Momjian committed
341 342 343
    teaches <SMALL>SQL</SMALL>. There is another PostgreSQL book at <A
    href=
    "http://www.commandprompt.com/ppbook/">http://www.commandprompt.com/ppbook.</A>
Bruce Momjian's avatar
Bruce Momjian committed
344 345 346
    There is a nice tutorial at <A href=
    "http://www.intermedia.net/support/sql/sqltut.shtm">http://www.intermedia.net/support/sql/sqltut.shtm,</A>
    at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
347
    "http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM">
Bruce Momjian's avatar
Bruce Momjian committed
348 349 350
    http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM,</A>
    and at <A href=
    "http://sqlcourse.com/">http://sqlcourse.com.</A></P>
Bruce Momjian's avatar
Bruce Momjian committed
351

Bruce Momjian's avatar
Bruce Momjian committed
352 353 354
    <P>Another one is "Teach Yourself SQL in 21 Days, Second Edition"
    at <A href=
    "http://members.tripod.com/er4ebus/sql/index.htm">http://members.tripod.com/er4ebus/sql/index.htm</A></P>
Bruce Momjian's avatar
Bruce Momjian committed
355

Bruce Momjian's avatar
Bruce Momjian committed
356 357 358
    <P>Many of our users like <I>The Practical SQL Handbook</I>,
    Bowman, Judith S., et al., Addison-Wesley. Others like <I>The
    Complete Reference SQL</I>, Groff et al., McGraw-Hill.</P>
Bruce Momjian's avatar
Bruce Momjian committed
359

360
    <H4><A name="1.10">1.10</A>) How do I join the development
Bruce Momjian's avatar
Bruce Momjian committed
361
    team?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
362

Bruce Momjian's avatar
Bruce Momjian committed
363 364 365 366 367
    <P>First, download the latest source and read the PostgreSQL
    Developers documentation on our web site, or in the distribution.
    Second, subscribe to the <I>pgsql-hackers</I> and
    <I>pgsql-patches</I> mailing lists. Third, submit high quality
    patches to pgsql-patches.</P>
Bruce Momjian's avatar
Bruce Momjian committed
368

Bruce Momjian's avatar
Bruce Momjian committed
369 370 371 372 373
    <P>There are about a dozen people who have commit privileges to the
    PostgreSQL <SMALL>CVS</SMALL> archive. They each have submitted so
    many high-quality patches that it was impossible for the existing
    committers to keep up, and we had confidence that patches they
    committed were of high quality.</P>
Bruce Momjian's avatar
Bruce Momjian committed
374

375
    <H4><A name="1.11">1.11</A>) How do I submit a bug report?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
376

377
    <P>Visit the PostgreSQL bug form at <A href=
Bruce Momjian's avatar
Bruce Momjian committed
378 379
    "http://www.postgresql.org/support/submitbug">
    http://www.postgresql.org/support/submitbug</A>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
380

Bruce Momjian's avatar
Bruce Momjian committed
381 382 383
    <P>Also check out our ftp site <A href=
    "ftp://ftp.PostgreSQL.org/pub">ftp://ftp.PostgreSQL.org/pub</A> to
    see if there is a more recent PostgreSQL version or patches.</P>
Bruce Momjian's avatar
Bruce Momjian committed
384

385
    <H4><A name="1.12">1.12</A>) How does PostgreSQL compare to other
Bruce Momjian's avatar
Bruce Momjian committed
386
    <SMALL>DBMS</SMALL>s?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
387

Bruce Momjian's avatar
Bruce Momjian committed
388 389
    <P>There are several ways of measuring software: features,
    performance, reliability, support, and price.</P>
Bruce Momjian's avatar
Bruce Momjian committed
390 391 392 393

    <DL>
      <DT><B>Features</B></DT>

Bruce Momjian's avatar
Bruce Momjian committed
394 395 396 397 398 399
      <DD>PostgreSQL has most features present in large commercial
      <SMALL>DBMS</SMALL>s, like transactions, subselects, triggers,
      views, foreign key referential integrity, and sophisticated
      locking. We have some features they do not have, like
      user-defined types, inheritance, rules, and multi-version
      concurrency control to reduce lock contention.<BR>
Bruce Momjian's avatar
Bruce Momjian committed
400 401 402 403 404
      <BR>
      </DD>

      <DT><B>Performance</B></DT>

Bruce Momjian's avatar
Bruce Momjian committed
405 406
      <DD>PostgreSQL's performance is comparable to other commercial and
      open source databases. It is faster for some things, slower for
Bruce Momjian's avatar
Bruce Momjian committed
407
      others. In comparison to MySQL or leaner database systems, we are
Bruce Momjian's avatar
Bruce Momjian committed
408
      faster for multiple users, complex queries, and a read/write query
Bruce Momjian's avatar
Bruce Momjian committed
409
      load.  MySQL is faster for simple SELECT queries done by a few users.
Bruce Momjian's avatar
Bruce Momjian committed
410
      Of course, MySQL does not have most of the features mentioned in the
Bruce Momjian's avatar
Bruce Momjian committed
411
      <I>Features</I> section above. We are built for reliability and
Bruce Momjian's avatar
Bruce Momjian committed
412
      features, and we continue to improve performance in every
Bruce Momjian's avatar
Bruce Momjian committed
413
      release. <BR>
Bruce Momjian's avatar
Bruce Momjian committed
414 415 416 417 418
      <BR>
      </DD>

      <DT><B>Reliability</B></DT>

Bruce Momjian's avatar
Bruce Momjian committed
419 420 421 422 423 424 425
      <DD>We realize that a <SMALL>DBMS</SMALL> must be reliable, or it
      is worthless. We strive to release well-tested, stable code that
      has a minimum of bugs. Each release has at least one month of
      beta testing, and our release history shows that we can provide
      stable, solid releases that are ready for production use. We
      believe we compare favorably to other database software in this
      area.<BR>
Bruce Momjian's avatar
Bruce Momjian committed
426 427 428 429 430
      <BR>
      </DD>

      <DT><B>Support</B></DT>

431 432
      <DD>Our mailing lists provide contact with a large group of developers
      and users to help resolve any problems encountered. While we cannot
Bruce Momjian's avatar
Bruce Momjian committed
433 434 435 436 437
      guarantee a fix, commercial <SMALL>DBMS</SMALL>s do not always
      supply a fix either. Direct access to developers, the user
      community, manuals, and the source code often make PostgreSQL
      support superior to other <SMALL>DBMS</SMALL>s. There is
      commercial per-incident support available for those who need it.
438
      (See <A href="#1.5">FAQ section 1.5</A>.)<BR>
Bruce Momjian's avatar
Bruce Momjian committed
439 440 441 442 443
      <BR>
      </DD>

      <DT><B>Price</B></DT>

Bruce Momjian's avatar
Bruce Momjian committed
444 445 446
      <DD>We are free for all use, both commercial and non-commercial.
      You can add our code to your product with no limitations, except
      those outlined in our BSD-style license stated above.<BR>
Bruce Momjian's avatar
Bruce Momjian committed
447 448 449
      <BR>
      </DD>
    </DL>
Bruce Momjian's avatar
Bruce Momjian committed
450

451
    <H4><A name="1.13">1.13</A>) How can I financially assist
Bruce Momjian's avatar
Bruce Momjian committed
452
    PostgreSQL?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
453

Bruce Momjian's avatar
Bruce Momjian committed
454
    <P>PostgreSQL has had a first-class infrastructure since we started
455
    in 1996. This is all thanks to Marc Fournier, who has created
Bruce Momjian's avatar
Bruce Momjian committed
456
    and managed this infrastructure over the years.</P>
Bruce Momjian's avatar
Bruce Momjian committed
457

Bruce Momjian's avatar
Bruce Momjian committed
458 459 460
    <P>Quality infrastructure is very important to an open-source
    project. It prevents disruptions that can greatly delay forward
    movement of the project.</P>
Bruce Momjian's avatar
Bruce Momjian committed
461

Bruce Momjian's avatar
Bruce Momjian committed
462 463 464
    <P>Of course, this infrastructure is not cheap. There are a variety
    of monthly and one-time expenses that are required to keep it
    going. If you or your company has money it can donate to help fund
465
    this effort, please go to <A href="http://store.pgsql.com/shopping/">http://store.pgsql.com/shopping/</A>
Bruce Momjian's avatar
Bruce Momjian committed
466
    and make a donation.</P>
Bruce Momjian's avatar
Bruce Momjian committed
467

Bruce Momjian's avatar
Bruce Momjian committed
468 469 470 471
    <P>Although the web page mentions PostgreSQL, Inc, the
    "contributions" item is solely to support the PostgreSQL project
    and does not fund any specific company. If you prefer, you can also
    send a check to the contact address.</P>
472

Bruce Momjian's avatar
Bruce Momjian committed
473 474 475
    <P>Also, if you have a success story about PostgreSQL, please email
    it to our advocacy list at <a href="mailto:pgsql-advocacy@postgresql.org">
    pgsql-advocacy@postgresql.org</a>.</P>
476

Bruce Momjian's avatar
Bruce Momjian committed
477
    <HR>
478

Bruce Momjian's avatar
Bruce Momjian committed
479
    <H2 align="center">User Client Questions</H2>
480

481 482
    <H4><A name="2.1">2.1</A>) What interfaces are available for
    PostgreSQL?</H4>
483

484 485 486 487
    <P>The PostgreSQL install includes only the <SMALL>C</SMALL> and embedded
    <SMALL>C</SMALL> interfaces.  All other interfaces are independent projects
    that are downloaded separately;  being separate allows them to have their
    own release schedule and development teams.</P>
488

489 490 491 492 493 494
    <P>Some programming languages like <SMALL>PHP</SMALL> include an
    interface to PostgreSQL. Interfaces for languages like Perl,
    <SMALL>TCL</SMALL>, Python, and many others are available at
    <a href="http://gborg.postgresql.org">http://gborg.postgresql.org</A>
    in the <I>Drivers/Interfaces</I> section and via Internet search.
    </P>
495

Bruce Momjian's avatar
Bruce Momjian committed
496 497
    <H4><A name="2.2">2.2</A>) What tools are available for using
    PostgreSQL with Web pages?</H4>
498

Bruce Momjian's avatar
Bruce Momjian committed
499 500
    <P>A nice introduction to Database-backed Web pages can be seen at:
    <A href="http://www.webreview.com">http://www.webreview.com</A></P>
501

Bruce Momjian's avatar
Bruce Momjian committed
502 503
    <P>For Web integration, PHP is an excellent interface. It is at <A
    href="http://www.php.net">http://www.php.net</A>.</P>
504

505
    <P>For complex cases, many use the Perl interface and CGI.pm or mod_perl.</P>
506

Bruce Momjian's avatar
Bruce Momjian committed
507 508
    <H4><A name="2.3">2.3</A>) Does PostgreSQL have a graphical user
    interface?</H4>
509

510
    <P>Yes, there are several graphical interfaces to PostgreSQL available.
Bruce Momjian's avatar
Bruce Momjian committed
511
    These include PgAccess <a href="http://www.pgaccess.org">
Bruce Momjian's avatar
Bruce Momjian committed
512
    http://www.pgaccess.org</a>), pgAdmin III (<a
513
    href="http://www.pgadmin.org">http://www.pgadmin.org</a>, RHDB Admin (<a
514
    href="http://sources.redhat.com/rhdb/">http://sources.redhat.com/rhdb/
Bruce Momjian's avatar
Bruce Momjian committed
515
    </a>), TORA (<a href="http://www.globecom.net/tora/">http://www.globecom.net/tora/</a>,
516 517 518
    partly commercial), and Rekall (<a href="http://www.rekallrevealed.org/">
    http://www.rekallrevealed.org/</a>). There is  also PhpPgAdmin
    (<a href="http://phppgadmin.sourceforge.net/">
519
    http://phppgadmin.sourceforge.net/ </a>), a web-based interface to 
520 521 522
    PostgreSQL.</P>

    <P>See <a href="http://techdocs.postgresql.org/guides/GUITools">http://techdocs.postgresql.org/guides/GUITools</a> for a more detailed list.</P>
523

Bruce Momjian's avatar
Bruce Momjian committed
524
    <HR>
525

526

Bruce Momjian's avatar
Bruce Momjian committed
527 528
    <H2 align="center">Administrative Questions</H2>

Bruce Momjian's avatar
Bruce Momjian committed
529 530 531 532 533 534
    <H4><A name="3.1">3.1</A>) How do I install PostgreSQL somewhere
    other than <I>/usr/local/pgsql</I>?</H4>

    <P>Specify the <I>--prefix</I> option when running
    <I>configure</I>.</P>

535
    <H4><A name="3.2">3.2</A>) How do I control connections from other
Bruce Momjian's avatar
Bruce Momjian committed
536
    hosts?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
537 538

    <P>By default, PostgreSQL only allows connections from the local
Bruce Momjian's avatar
Bruce Momjian committed
539 540 541 542 543
    machine using Unix domain sockets or TCP/IP connections. Other
    machines will not be able to connect unless you modify
    listen_addresses in the postgresql.conf <B>and</B> enable
    host-based authentication by modifying the file
    <I>$PGDATA/pg_hba.conf</I> accordingly.</P>
Bruce Momjian's avatar
Bruce Momjian committed
544

545
    <H4><A name="3.3">3.3</A>) How do I tune the database engine for
Bruce Momjian's avatar
Bruce Momjian committed
546 547 548
    better performance?</H4>

    <P>Certainly, indexes can speed up queries. The
549 550 551
    <SMALL>EXPLAIN ANALYZE</SMALL> command allows you to see how
    PostgreSQL is interpreting your query, and which indexes are
    being used.</P>
Bruce Momjian's avatar
Bruce Momjian committed
552 553 554

    <P>If you are doing many <SMALL>INSERTs</SMALL>, consider doing
    them in a large batch using the <SMALL>COPY</SMALL> command. This
Bruce Momjian's avatar
Bruce Momjian committed
555
    is much faster than individual <SMALL>INSERTS</SMALL>. Second,
Bruce Momjian's avatar
Bruce Momjian committed
556 557 558 559 560 561
    statements not in a <SMALL>BEGIN WORK/COMMIT</SMALL> transaction
    block are considered to be in their own transaction. Consider
    performing several statements in a single transaction block. This
    reduces the transaction overhead. Also, consider dropping and
    recreating indexes when making large data changes.</P>

562 563 564 565
    <P>There are several tuning options in the <a href=
    "http://www.postgresql.org/docs/current/static/runtime.html">
    Administration Guide/Server Run-time Environment/Run-time Configuration</a>.
    You can disable <I>fsync()</I> by using <i>fsync</I> option. This will
Bruce Momjian's avatar
Bruce Momjian committed
566 567 568
    prevent <I>fsync()</I>s from flushing to disk after every
    transaction.</P>

569
    <P>You can use the <I>shared_buffers</I> option to
Bruce Momjian's avatar
Bruce Momjian committed
570 571 572 573
    increase the number of shared memory buffers used by the backend
    processes. If you make this parameter too high, the
    <I>postmaster</I> may not start because you have exceeded your
    kernel's limit on shared memory space. Each buffer is 8K and the
574
    default is 1000 buffers.</P>
Bruce Momjian's avatar
Bruce Momjian committed
575

Bruce Momjian's avatar
Bruce Momjian committed
576 577 578
    <P>You can also use the <I>sort_mem</I> (from PostgreSQL 8.0: <I>work_mem</I>)
    options to increase the maximum amount of memory used by the backend
    processes for each temporary sort. The default is 1024 (i.e. 1MB).</P>
Bruce Momjian's avatar
Bruce Momjian committed
579 580 581 582 583

    <P>You can also use the <SMALL>CLUSTER</SMALL> command to group
    data in tables to match an index. See the <SMALL>CLUSTER</SMALL>
    manual page for more details.</P>

584
    <H4><A name="3.4">3.4</A>) What debugging features are
Bruce Momjian's avatar
Bruce Momjian committed
585 586 587 588 589 590 591 592 593 594 595 596 597
    available?</H4>

    <P>PostgreSQL has several features that report status information
    that can be valuable for debugging purposes.</P>

    <P>First, by running <I>configure</I> with the --enable-cassert
    option, many <I>assert()</I>s monitor the progress of the backend
    and halt the program when something unexpected occurs.</P>

    <P>Both <I>postmaster</I> and <I>postgres</I> have several debug
    options available. First, whenever you start <I>postmaster</I>,
    make sure you send the standard output and error to a log file,
    like:</P>
598
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
599 600 601
    cd /usr/local/pgsql
    ./bin/postmaster &gt;server.log 2&gt;&amp;1 &amp;
</PRE>
602

Bruce Momjian's avatar
Bruce Momjian committed
603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622
    <P>This will put a server.log file in the top-level PostgreSQL
    directory. This file contains useful information about problems or
    errors encountered by the server. <I>Postmaster</I> has a <I>-d</I>
    option that allows even more detailed information to be reported.
    The <I>-d</I> option takes a number that specifies the debug level.
    Be warned that high debug level values generate large log
    files.</P>

    <P>If <I>postmaster</I> is not running, you can actually run the
    <I>postgres</I> backend from the command line, and type your
    <SMALL>SQL</SMALL> statement directly. This is recommended
    <B>only</B> for debugging purposes. Note that a newline terminates
    the query, not a semicolon. If you have compiled with debugging
    symbols, you can use a debugger to see what is happening. Because
    the backend was not started from <I>postmaster</I>, it is not
    running in an identical environment and locking/backend interaction
    problems may not be duplicated.</P>

    <P>If <I>postmaster</I> is running, start <I>psql</I> in one
    window, then find the <SMALL>PID</SMALL> of the <I>postgres</I>
Bruce Momjian's avatar
Bruce Momjian committed
623
    process used by <I>psql</I> using <CODE>SELECT pg_backend_pid()</CODE>.
624 625
    Use a debugger to attach to the <I>postgres</I> <SMALL>PID</SMALL>.
    You can set breakpoints in the debugger and issue queries from
Bruce Momjian's avatar
Bruce Momjian committed
626

627 628 629 630 631 632
    <I>psql</I>. If you are debugging <I>postgres</I> startup, you can
    set PGOPTIONS="-W n", then start <I>psql</I>. This will cause startup
    to delay for <I>n</I> seconds so you can attach to the process with
    the debugger, set any breakpoints, and continue through the startup
    sequence.</P>

Bruce Momjian's avatar
Bruce Momjian committed
633
    <P>There are several <CODE>log_*</CODE> server configuration variables
634 635
    that enable printing of process statistics which can be very useful
    for debugging and performance measurements.</P>
Bruce Momjian's avatar
Bruce Momjian committed
636 637 638 639

    <P>You can also compile with profiling to see what functions are
    taking execution time. The backend profile files will be deposited
    in the <I>pgsql/data/base/dbname</I> directory. The client profile
640
    file will be put in the client's current directory. Linux requires
641
    a compile with <I>-DLINUX_PROFILE</I> for proper profiling.</P>
Bruce Momjian's avatar
Bruce Momjian committed
642

643
    <H4><A name="3.5">3.5</A>) Why do I get <I>"Sorry, too many
Bruce Momjian's avatar
Bruce Momjian committed
644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663
    clients"</I> when trying to connect?</H4>

    <P>You need to increase <I>postmaster</I>'s limit on how many
    concurrent backend processes it can start.</P>

    <P>The default limit is 32 processes. You can increase it by
    restarting <I>postmaster</I> with a suitable <I>-N</I> value or
    modifying <I>postgresql.conf</I>.</P>

    <P>Note that if you make <I>-N</I> larger than 32, you must also
    increase <I>-B</I> beyond its default of 64; <I>-B</I> must be at
    least twice <I>-N</I>, and probably should be more than that for
    best performance. For large numbers of backend processes, you are
    also likely to find that you need to increase various Unix kernel
    configuration parameters. Things to check include the maximum size
    of shared memory blocks, <SMALL>SHMMAX;</SMALL> the maximum number
    of semaphores, <SMALL>SEMMNS</SMALL> and <SMALL>SEMMNI;</SMALL> the
    maximum number of processes, <SMALL>NPROC;</SMALL> the maximum
    number of processes per user, <SMALL>MAXUPRC;</SMALL> and the
    maximum number of open files, <SMALL>NFILE</SMALL> and
Bruce Momjian's avatar
Bruce Momjian committed
664
    <SMALL>NINODE</SMALL>. The reason that PostgreSQL has a limit on
Bruce Momjian's avatar
Bruce Momjian committed
665 666 667
    the number of allowed backend processes is so your system won't run
    out of resources.</P>

668
    <H4><A name="3.6">3.6</A>) What is in the <I>pgsql_tmp</I> directory?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
669

Bruce Momjian's avatar
Bruce Momjian committed
670 671 672 673 674
    <P>This directory contains temporary files generated by the query 
    executor. For example, if a sort needs to be done to satisfy an 
    <SMALL>ORDER BY</SMALL> and the sort requires more space than the
    backend's <I>-S</I> parameter allows, then temporary files are created
    here to hold the extra data.</P>
Bruce Momjian's avatar
Bruce Momjian committed
675

676 677 678
    <P>The temporary files are usually deleted automatically, but might
    remain if a backend crashes during a sort. A stop and restart of the
    <I>postmaster</I> will remove files from those directories.</P>
679

680
    <H4><A name="3.7">3.7</A>) Why do I need to do a dump and restore
681 682 683 684
    to upgrade between major PostgreSQL releases?</H4>

    <P>The PostgreSQL team makes only small changes between minor releases,
    so upgrading from 7.2 to 7.2.1 does not require a dump and restore.
685 686 687 688 689
    However, major releases (e.g. from 7.2 to 7.3) often change the internal
    format of system tables and data files. These changes are often complex,
    so we don't maintain backward compatability for data files. A dump outputs
    data in a generic format that can then be loaded in using the new internal
    format.</P>
690

691 692 693 694
    <P>In releases where the on-disk format does not change, the
    <I>pg_upgrade</I> script can be used to upgrade without a dump/restore.
    The release notes mention whether <I>pg_upgrade</I> is available for the
    release.</P>
695

696
    <H4><A name="3.8">3.8</A>) What computer hardware should I use?</H4>
697 698 699 700

    <P>Because PC hardware is mostly compatible, people tend to believe that
    all PC hardware is of equal quality.  It is not.  ECC RAM, SCSI, and
    quality motherboards are more reliable and have better performance than
Bruce Momjian's avatar
Bruce Momjian committed
701
    less expensive hardware.  PostgreSQL will run on almost any hardware,
Bruce Momjian's avatar
Bruce Momjian committed
702 703 704
    but if reliability and performance are important it is wise to
    research your hardware options thoroughly.  Our email lists can be used
    to discuss hardware options and tradeoffs.</P>
705

Bruce Momjian's avatar
Bruce Momjian committed
706 707
    <HR>

Bruce Momjian's avatar
Bruce Momjian committed
708
    <H2 align="center">Operational Questions</H2>
Bruce Momjian's avatar
Bruce Momjian committed
709

710
    <H4><A name="4.1">4.1</A>) How do I <SMALL>SELECT</SMALL> only the
711
    first few rows of a query?  A random row?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
712

Bruce Momjian's avatar
Bruce Momjian committed
713 714
    <P>See the <SMALL>FETCH</SMALL> manual page, or use
    <SMALL>SELECT</SMALL> ... <SMALL>LIMIT</SMALL>....</P>
Bruce Momjian's avatar
Bruce Momjian committed
715

Bruce Momjian's avatar
Bruce Momjian committed
716
    <P>The entire query may have to be evaluated, even if you only want
717
    the first few rows. Consider using a query that has an <SMALL>ORDER
Bruce Momjian's avatar
Bruce Momjian committed
718
    BY</SMALL>. If there is an index that matches the <SMALL>ORDER
Bruce Momjian's avatar
Bruce Momjian committed
719 720 721
    BY</SMALL>, PostgreSQL may be able to evaluate only the first few
    records requested, or the entire query may have to be evaluated
    until the desired rows have been generated.</P>
Bruce Momjian's avatar
Bruce Momjian committed
722

723 724 725 726 727 728 729 730
    <P>To <SMALL>SELECT</SMALL> a random row, use:
<PRE>
    SELECT col
    FROM tab
    ORDER BY random()
    LIMIT 1;
</PRE>

731
    <H4><A name="4.2">4.2</A>) How do I find out what tables, indexes,
732 733
    databases, and users are defined?  How do I see the queries used
    by <I>psql</I> to display them?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
734

Bruce Momjian's avatar
Bruce Momjian committed
735 736 737 738 739 740
    <P>Use the \dt command to see tables in <I>psql</I>. For a complete list of
    commands inside psql you can use \?. Alternatively you can read the source 
    code for <I>psql</I> in file <I>pgsql/src/bin/psql/describe.c</I>, it 
    contains <SMALL>SQL</SMALL> commands that generate the output for 
    <I>psql</I>'s backslash commands. You can also start <I>psql</I> with the
    <I>-E</I> option so it will print out the queries it uses to execute the 
741
    commands you give. PostgreSQL also provides an <SMALL>SQL</SMALL> compliant
Bruce Momjian's avatar
Bruce Momjian committed
742 743
    INFORMATION SCHEMA interface you can query to get information about the
    database.</P>
Bruce Momjian's avatar
Bruce Momjian committed
744

745 746 747 748 749 750 751
    <P>There are also system tables beginning with <I>pg_</I> that describe
    these too. Use <I>psql -l</I> will list all databases.</P>

    <P>Also try the file <I>pgsql/src/tutorial/syscat.source</I>. It
    illustrates many of the <SMALL>SELECT</SMALL>s needed to get
    information from the database system tables.</P>

752
    <H4><A name="4.3">4.3</A>) How do you remove a column from a
753
    table, or change its data type?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
754

755 756
    <P><SMALL>DROP COLUMN</SMALL> functionality was added in release 7.3
    with <SMALL>ALTER TABLE DROP COLUMN</SMALL>.  In earlier versions,
757
    you can do this:</P>
758
<PRE>
759 760
    BEGIN;
    LOCK TABLE old_table;
Bruce Momjian's avatar
Bruce Momjian committed
761 762 763 764 765
    SELECT ...  -- select all columns but the one you want to remove
    INTO TABLE new_table
    FROM old_table;
    DROP TABLE old_table;
    ALTER TABLE new_table RENAME TO old_table;
766
    COMMIT;
Bruce Momjian's avatar
Bruce Momjian committed
767
</PRE>
768

769 770 771 772
    <P>Changing the data type of a column can be done easily in 8.0
    and later with <SMALL>ALTER TABLE ALTER COLUMN TYPE</SMALL>.

    <P>In earlier releases, do this:</P>
773 774 775 776
<PRE>
    BEGIN;
    ALTER TABLE tab ADD COLUMN new_col <i>new_data_type</i>;
    UPDATE tab SET new_col = CAST(old_col AS <i>new_data_type</i>);
Bruce Momjian's avatar
Bruce Momjian committed
777
    ALTER TABLE tab DROP COLUMN old_col;
778 779 780 781 782
    COMMIT;
</PRE>
    <P>You might then want to do <I>VACUUM FULL tab</I> to reclaim the
    disk space used by the expired rows.</P>
    
783
    <H4><A name="4.4">4.4</A>) What is the maximum size for a row, a
Bruce Momjian's avatar
Bruce Momjian committed
784
    table, and a database?</H4>
785

Bruce Momjian's avatar
Bruce Momjian committed
786
    <P>These are the limits:</P>
787
<PRE>
788
    Maximum size for a database?             unlimited (32 TB databases exist)
789
    Maximum size for a table?                32 TB
Bruce Momjian's avatar
Bruce Momjian committed
790 791
    Maximum size for a row?                  1.6TB
    Maximum size for a field?                1 GB
Bruce Momjian's avatar
Bruce Momjian committed
792 793 794
    Maximum number of rows in a table?       unlimited
    Maximum number of columns in a table?    250-1600 depending on column types
    Maximum number of indexes on a table?    unlimited
795
</PRE>
796

Bruce Momjian's avatar
Bruce Momjian committed
797 798 799
    Of course, these are not actually unlimited, but limited to
    available disk space and memory/swap space. Performance may suffer
    when these values get unusually large. 
800

801
    <P>The maximum table size of 32 TB does not require large file
Bruce Momjian's avatar
Bruce Momjian committed
802 803 804
    support from the operating system. Large tables are stored as
    multiple 1 GB files so file system size limits are not
    important.</P>
Bruce Momjian's avatar
Bruce Momjian committed
805

Bruce Momjian's avatar
Bruce Momjian committed
806
    <P>The maximum table size and maximum number of columns can be
807
    quadrupled by increasing the default block size to 32k.</P>
Bruce Momjian's avatar
Bruce Momjian committed
808

809
    <H4><A name="4.5">4.5</A>) How much database disk space is required
Bruce Momjian's avatar
Bruce Momjian committed
810
    to store data from a typical text file?</H4>
811

Bruce Momjian's avatar
Bruce Momjian committed
812 813
    <P>A PostgreSQL database may require up to five times the disk
    space to store data from a text file.</P>
814

Bruce Momjian's avatar
Bruce Momjian committed
815
    <P>As an example, consider a file of 100,000 lines with an integer
Bruce Momjian's avatar
Bruce Momjian committed
816 817 818 819
    and text description on each line. Suppose the text string
    avergages twenty bytes in length. The flat file would be 2.8 MB.
    The size of the PostgreSQL database file containing this data can
    be estimated as 6.4 MB:</P>
820
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
821
    32 bytes: each row header (approximate)
822
    24 bytes: one int field and one text field
823 824
   + 4 bytes: pointer on page to tuple
   ----------------------------------------
Bruce Momjian's avatar
Bruce Momjian committed
825
    60 bytes per row
826 827 828 829

   The data page size in PostgreSQL is 8192 bytes (8 KB), so:

   8192 bytes per page
Bruce Momjian's avatar
Bruce Momjian committed
830 831
   -------------------   =  136 rows per database page (rounded down)
     60 bytes per row
832

Bruce Momjian's avatar
Bruce Momjian committed
833
   100000 data rows
Bruce Momjian's avatar
Bruce Momjian committed
834
   --------------------  =  735 database pages (rounded up)
Bruce Momjian's avatar
Bruce Momjian committed
835
      128 rows per page
836

Bruce Momjian's avatar
Bruce Momjian committed
837
735 database pages * 8192 bytes per page  =  6,021,120 bytes (6 MB)
Bruce Momjian's avatar
Bruce Momjian committed
838
</PRE>
839

Bruce Momjian's avatar
Bruce Momjian committed
840 841
    <P>Indexes do not require as much overhead, but do contain the data
    that is being indexed, so they can be large also.</P>
Bruce Momjian's avatar
Bruce Momjian committed
842

843
    <P><SMALL>NULL</SMALL>s are stored as bitmaps, so they
Bruce Momjian's avatar
Bruce Momjian committed
844 845
    use very little space.</P>
    
846
    <H4><A name="4.6">4.6</A>) Why are my queries slow?  Why don't they
847
    use my indexes?</H4>
848 849

    <P>Indexes are not automatically used by every query. Indexes are only
Bruce Momjian's avatar
Bruce Momjian committed
850
    used if the table is larger than a minimum size, and the query
Bruce Momjian's avatar
Bruce Momjian committed
851
    selects only a small percentage of the rows in the table. This is
852
    because the random disk access caused by an index scan can be
Bruce Momjian's avatar
Bruce Momjian committed
853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868
    slower than a straight read through the table, or sequential scan. 

    <P>To determine if an index should be used, PostgreSQL must have
    statistics about the table. These statistics are collected using
    <SMALL>VACUUM ANALYZE</SMALL>, or simply <SMALL>ANALYZE</SMALL>.
    Using statistics, the optimizer knows how many rows are in the
    table, and can better determine if indexes should be used.
    Statistics are also valuable in determining optimal join order and
    join methods. Statistics collection should be performed
    periodically as the contents of the table change.</P>

    <P>Indexes are normally not used for <SMALL>ORDER BY</SMALL> or to
    perform joins. A sequential scan followed by an explicit sort is
    usually faster than an index scan of a large table.</P>
    However, <SMALL>LIMIT</SMALL> combined with <SMALL>ORDER BY</SMALL>
    often will use an index because only a small portion of the table
869 870 871 872 873 874
    is returned.  In fact, though MAX() and MIN() don't use indexes,
    it is possible to retrieve such values using an index with ORDER BY
    and LIMIT:
<PRE>
    SELECT col
    FROM tab
Bruce Momjian's avatar
Bruce Momjian committed
875
    ORDER BY col [ DESC ]
876
    LIMIT 1;
877
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
878

Bruce Momjian's avatar
Bruce Momjian committed
879 880 881 882
    <P>If you believe the optimizer is incorrect in choosing a
    sequential scan, use <CODE>SET enable_seqscan TO 'off'</CODE> and
    run tests to see if an index scan is indeed faster.</P>

Bruce Momjian's avatar
Bruce Momjian committed
883
    <P>When using wild-card operators such as <SMALL>LIKE</SMALL> or
Bruce Momjian's avatar
Bruce Momjian committed
884
    <I>~</I>, indexes can only be used in certain circumstances:</P>
885 886
    <UL>
    <LI>The beginning of the search string must be anchored to the start
Bruce Momjian's avatar
Bruce Momjian committed
887
    of the string, i.e.
888
    <UL>
Bruce Momjian's avatar
Bruce Momjian committed
889
    <LI><SMALL>LIKE</SMALL> patterns must not start with <I>%</I>.</LI>
Bruce Momjian's avatar
Bruce Momjian committed
890
    <LI><I>~</I> (regular expression) patterns must start with
Bruce Momjian's avatar
Bruce Momjian committed
891 892
    <I>^</I>.</LI>
    </UL></LI>
893 894
    <LI>The search string can not start with a character class,
    e.g. [a-e].</LI>
895
    <LI>Case-insensitive searches such as <SMALL>ILIKE</SMALL> and
Bruce Momjian's avatar
Bruce Momjian committed
896
    <I>~*</I> do not utilize indexes. Instead, use functional
897
    indexes, which are described in section <a href="#4.10">4.10</a>.</LI>
898
    <LI>The default <I>C</I> locale must be used during
899 900
    <i>initdb</i> because it is not possible to know the next-greater
    character in a non-C locale.  You can create a special
Bruce Momjian's avatar
Bruce Momjian committed
901 902
    <CODE>text_pattern_ops</CODE> index for such cases that work only
    for <SMALL>LIKE</SMALL> indexing.
903
    </LI>
904
    </UL>
Bruce Momjian's avatar
Bruce Momjian committed
905

Bruce Momjian's avatar
Bruce Momjian committed
906 907 908 909
    <P>In pre-8.0 releases, indexes often can not be used unless the data
    types exactly match the index's column types.  This is particularly
    true of int2, int8, and numeric column indexes.</P>

910
    <H4><A name="4.7">4.7</A>) How do I see how the query optimizer is
Bruce Momjian's avatar
Bruce Momjian committed
911
    evaluating my query?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
912 913 914

    <P>See the <SMALL>EXPLAIN</SMALL> manual page.</P>

915
    <H4><A name="4.8">4.8</A>) How do I perform regular expression
Bruce Momjian's avatar
Bruce Momjian committed
916 917
    searches and case-insensitive regular expression searches? How do I
    use an index for case-insensitive searches?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
918

Bruce Momjian's avatar
Bruce Momjian committed
919 920 921
    <P>The <I>~</I> operator does regular expression matching, and
    <I>~*</I> does case-insensitive regular expression matching. The
    case-insensitive variant of <SMALL>LIKE</SMALL> is called
922
    <SMALL>ILIKE</SMALL>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
923

Bruce Momjian's avatar
Bruce Momjian committed
924 925
    <P>Case-insensitive equality comparisons are normally expressed
    as:</P>
Bruce Momjian's avatar
Bruce Momjian committed
926
<PRE>
927 928
    SELECT *
    FROM tab
929
    WHERE lower(col) = 'abc';
Bruce Momjian's avatar
Bruce Momjian committed
930
</PRE>
931

Bruce Momjian's avatar
Bruce Momjian committed
932 933
    This will not use an standard index. However, if you create a
    functional index, it will be used: 
Bruce Momjian's avatar
Bruce Momjian committed
934
<PRE>
935
    CREATE INDEX tabindex ON tab (lower(col));
Bruce Momjian's avatar
Bruce Momjian committed
936
</PRE>
937

938
    <H4><A name="4.9">4.9</A>) In a query, how do I detect if a field
Bruce Momjian's avatar
Bruce Momjian committed
939
    is <SMALL>NULL</SMALL>?</H4>
940

Bruce Momjian's avatar
Bruce Momjian committed
941 942
    <P>You test the column with <SMALL>IS NULL</SMALL> and <SMALL>IS
    NOT NULL</SMALL>.</P>
943

944
    <H4><A name="4.10">4.10</A>) What is the difference between the
Bruce Momjian's avatar
Bruce Momjian committed
945
    various character types?</H4>
946 947 948
<PRE>
Type            Internal Name   Notes
--------------------------------------------------
949
VARCHAR(n)      varchar         size specifies maximum length, no padding
Bruce Momjian's avatar
Bruce Momjian committed
950
CHAR(n)         bpchar          blank padded to the specified fixed length
951
TEXT            text            no specific upper limit on length
Bruce Momjian's avatar
Bruce Momjian committed
952
BYTEA           bytea           variable-length byte array (null-byte safe)
953
"char"          char            one character
Bruce Momjian's avatar
Bruce Momjian committed
954
</PRE>
955

Bruce Momjian's avatar
Bruce Momjian committed
956 957 958
    <P>You will see the internal name when examining system catalogs
    and in some error messages.</P>

959
    <P>The first four types above are "varlena" types (i.e., the first
Bruce Momjian's avatar
Bruce Momjian committed
960 961 962 963 964 965
    four bytes on disk are the length, followed by the data). Thus the
    actual space used is slightly greater than the declared size.
    However, these data types are also subject to compression or being
    stored out-of-line by <SMALL>TOAST</SMALL>, so the space on disk
    might also be less than expected.</P>

Bruce Momjian's avatar
Bruce Momjian committed
966 967 968 969 970 971 972
    <SMALL>VARCHAR(n)</SMALL> is best when storing variable-length
    strings and it limits how long a string can be. <SMALL>TEXT</SMALL>
    is for strings of unlimited length, with a maximum of one gigabyte.
    <P><SMALL>CHAR(n)</SMALL> is for storing strings that are all the
    same length. <SMALL>CHAR(n)</SMALL> pads with blanks to the specified
    length, while <SMALL>VARCHAR(n)</SMALL> only stores the characters
    supplied.  <SMALL>BYTEA</SMALL> is for storing binary data,
973 974
    particularly values that include <SMALL>NULL</SMALL> bytes. All the
    types described here have similar performance characteristics.</P>
Bruce Momjian's avatar
Bruce Momjian committed
975

976
    <H4><A name="4.11.1">4.11.1</A>) How do I create a
Bruce Momjian's avatar
Bruce Momjian committed
977 978 979
    serial/auto-incrementing field?</H4>

    <P>PostgreSQL supports a <SMALL>SERIAL</SMALL> data type. It
980
    auto-creates a sequence.  For example,
Bruce Momjian's avatar
Bruce Momjian committed
981
    this:</P>
982
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
983 984 985 986
    CREATE TABLE person ( 
        id   SERIAL, 
        name TEXT 
    );
987
</PRE>
988

Bruce Momjian's avatar
Bruce Momjian committed
989
    is automatically translated into this: 
990
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
991 992 993 994 995
    CREATE SEQUENCE person_id_seq;
    CREATE TABLE person ( 
        id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
        name TEXT 
    );
996
</PRE>
997

Bruce Momjian's avatar
Bruce Momjian committed
998 999 1000 1001 1002 1003
    See the <I>create_sequence</I> manual page for more information
    about sequences. You can also use each row's <I>OID</I> field as a
    unique value. However, if you need to dump and reload the database,
    you need to use <I>pg_dump</I>'s <I>-o</I> option or <SMALL>COPY
    WITH OIDS</SMALL> option to preserve the <SMALL>OID</SMALL>s. 

1004
    <H4><A name="4.11.2">4.11.2</A>) How do I get the value of a
Bruce Momjian's avatar
Bruce Momjian committed
1005 1006 1007 1008 1009
    <SMALL>SERIAL</SMALL> insert?</H4>

    <P>One approach is to retrieve the next <SMALL>SERIAL</SMALL> value
    from the sequence object with the <I>nextval()</I> function
    <I>before</I> inserting and then insert it explicitly. Using the
1010
    example table in <A href="#4.11.1">4.11.1</A>, an example in a
1011
    pseudo-language would look like this:</P>
1012
<PRE>
1013 1014
    new_id = execute("SELECT nextval('person_id_seq')");
    execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
1015
</PRE>
1016

Bruce Momjian's avatar
Bruce Momjian committed
1017 1018 1019 1020 1021 1022
    You would then also have the new value stored in
    <CODE>new_id</CODE> for use in other queries (e.g., as a foreign
    key to the <CODE>person</CODE> table). Note that the name of the
    automatically created <SMALL>SEQUENCE</SMALL> object will be named
    &lt;<I>table</I>&gt;_&lt;<I>serialcolumn</I>&gt;_<I>seq</I>, where
    <I>table</I> and <I>serialcolumn</I> are the names of your table
1023
    and your <SMALL>SERIAL</SMALL> column, respectively.
Bruce Momjian's avatar
Bruce Momjian committed
1024 1025

    <P>Alternatively, you could retrieve the assigned
1026
    <SMALL>SERIAL</SMALL> value with the <I>currval()</I> function
Bruce Momjian's avatar
Bruce Momjian committed
1027
    <I>after</I> it was inserted by default, e.g.,</P>
1028
<PRE>
1029 1030
    execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
    new_id = execute("SELECT currval('person_id_seq')");
1031
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1032
    
1033
    <P>Finally, you could use the <A href="#4.12"><SMALL>OID</SMALL></A>
Bruce Momjian's avatar
Bruce Momjian committed
1034
    returned from the <SMALL>INSERT</SMALL> statement to look up the
1035 1036
    default value, though this is probably the least portable approach,
    and the oid value will wrap around when it reaches 4 billion.
Bruce Momjian's avatar
Bruce Momjian committed
1037 1038 1039
    In Perl, using DBI with the DBD::Pg module, the oid value is made
    available via <I>$sth-&gt;{pg_oid_status}</I> after
    <I>$sth-&gt;execute()</I>.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1040

1041
    <H4><A name="4.11.3">4.11.3</A>) Doesn't <I>currval()</I>
1042
    lead to a race condition with other users?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1043

1044
    <P>No. <I>currval()</I> returns the current value assigned by your
Bruce Momjian's avatar
Bruce Momjian committed
1045 1046
    backend, not by all users.</P>

1047
    <H4><A name="4.11.4">4.11.4</A>) Why aren't my sequence numbers
Bruce Momjian's avatar
Bruce Momjian committed
1048 1049
    reused on transaction abort? Why are there gaps in the numbering of
    my sequence/SERIAL column?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1050 1051

    <P>To improve concurrency, sequence values are given out to running
Bruce Momjian's avatar
Bruce Momjian committed
1052
    transactions as needed and are not locked until the transaction
Bruce Momjian's avatar
Bruce Momjian committed
1053 1054
    completes. This causes gaps in numbering from aborted
    transactions.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1055

1056
    <H4><A name="4.12">4.12</A>) What is an <SMALL>OID</SMALL>? What is
Bruce Momjian's avatar
Bruce Momjian committed
1057 1058
    a <SMALL>TID</SMALL>?</H4>

Bruce Momjian's avatar
Bruce Momjian committed
1059 1060 1061 1062 1063 1064 1065 1066 1067
    <P>Every row that is created in PostgreSQL gets a unique
    <SMALL>OID</SMALL> unless created <SMALL>WITHOUT OIDS</SMALL>.
    O<SMALL>ID</SMALL>s are autotomatically assigned unique 4-byte
    integers that are unique across the entire installation.  However,
    they overflow at 4 billion, and then the O<SMALL>ID</SMALL>s start
    being duplicated. PostgreSQL uses <SMALL>OID</SMALL>s to link its
    internal system tables together.</P>

    <P>To uniquely number columns in user tables, it is best to use
Bruce Momjian's avatar
Bruce Momjian committed
1068 1069
    <SMALL>SERIAL</SMALL> rather than O<SMALL>ID</SMALL>s because
    <SMALL>SERIAL</SMALL> sequences are unique only within a single
Bruce Momjian's avatar
Bruce Momjian committed
1070 1071 1072
    table. and are therefore less likely to overflow.
    <SMALL>SERIAL8</SMALL> is available for storing eight-byte sequence
    values.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1073

Bruce Momjian's avatar
Bruce Momjian committed
1074 1075 1076 1077
    <P>T<SMALL>ID</SMALL>s are used to identify specific physical rows
    with block and offset values. T<SMALL>ID</SMALL>s change after rows
    are modified or reloaded. They are used by index entries to point
    to physical rows.</P>
1078

1079
    <H4><A name="4.13">4.13</A>) What is the meaning of some of the
Bruce Momjian's avatar
Bruce Momjian committed
1080
    terms used in PostgreSQL?</H4>
1081

Bruce Momjian's avatar
Bruce Momjian committed
1082 1083
    <P>Some of the source code and older documentation use terms that
    have more common usage. Here are some:</P>
1084

Bruce Momjian's avatar
Bruce Momjian committed
1085 1086
    <UL>
      <LI>table, relation, class</LI>
1087

Bruce Momjian's avatar
Bruce Momjian committed
1088
      <LI>row, record, tuple</LI>
1089

Bruce Momjian's avatar
Bruce Momjian committed
1090
      <LI>column, field, attribute</LI>
Bruce Momjian's avatar
Bruce Momjian committed
1091

Bruce Momjian's avatar
Bruce Momjian committed
1092
      <LI>retrieve, select</LI>
1093

Bruce Momjian's avatar
Bruce Momjian committed
1094
      <LI>replace, update</LI>
1095

Bruce Momjian's avatar
Bruce Momjian committed
1096 1097 1098 1099 1100 1101 1102 1103 1104
      <LI>append, insert</LI>

      <LI><SMALL>OID</SMALL>, serial value</LI>

      <LI>portal, cursor</LI>

      <LI>range variable, table name, table alias</LI>
    </UL>

Bruce Momjian's avatar
Bruce Momjian committed
1105
    <P>A list of general database terms can be found at: <A href=
1106
    "http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html">http://hea-www.harvard.edu/MST/simul/software/docs/pkgs/pgsql/glossary/glossary.html</A></P>
1107

1108
    <H4><A name="4.14">4.14</A>) Why do I get the error <I>"ERROR:
Bruce Momjian's avatar
Bruce Momjian committed
1109
    Memory exhausted in AllocSetAlloc()"</I>?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1110

1111 1112 1113
    <P>You probably have run out of virtual memory on your system,
    or your kernel has a low limit for certain resources. Try this
    before starting <I>postmaster</I>:</P>
Bruce Momjian's avatar
Bruce Momjian committed
1114 1115 1116 1117
<PRE>
    ulimit -d 262144
    limit datasize 256m
</PRE>
1118

Bruce Momjian's avatar
Bruce Momjian committed
1119 1120 1121 1122 1123 1124
    Depending on your shell, only one of these may succeed, but it will
    set your process data segment limit much higher and perhaps allow
    the query to complete. This command applies to the current process,
    and all subprocesses created after the command is run. If you are
    having a problem with the <SMALL>SQL</SMALL> client because the
    backend is returning too much data, try it before starting the
1125
    client.
Bruce Momjian's avatar
Bruce Momjian committed
1126

1127
    <H4><A name="4.15">4.15</A>) How do I tell what PostgreSQL version
Bruce Momjian's avatar
Bruce Momjian committed
1128
    I am running?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1129

1130
    <P>From <I>psql</I>, type <CODE>SELECT version();</CODE></P>
Bruce Momjian's avatar
Bruce Momjian committed
1131

1132
    <H4><A name="4.16">4.16</A>) Why does my large-object operations
Bruce Momjian's avatar
Bruce Momjian committed
1133
    get <I>"invalid large obj descriptor"</I>?</H4>
1134

Bruce Momjian's avatar
Bruce Momjian committed
1135 1136 1137
    <P>You need to put <CODE>BEGIN WORK</CODE> and <CODE>COMMIT</CODE>
    around any use of a large object handle, that is, surrounding
    <CODE>lo_open</CODE> ... <CODE>lo_close.</CODE></P>
1138

Bruce Momjian's avatar
Bruce Momjian committed
1139 1140 1141 1142 1143
    <P>Currently PostgreSQL enforces the rule by closing large object
    handles at transaction commit. So the first attempt to do anything
    with the handle will draw <I>invalid large obj descriptor</I>. So
    code that used to work (at least most of the time) will now
    generate that error message if you fail to use a transaction.</P>
1144

Bruce Momjian's avatar
Bruce Momjian committed
1145 1146
    <P>If you are using a client interface like <SMALL>ODBC</SMALL> you
    may need to set <CODE>auto-commit off.</CODE></P>
Bruce Momjian's avatar
Bruce Momjian committed
1147

1148
    <H4><A name="4.17">4.17</A>) How do I create a column that will
Bruce Momjian's avatar
Bruce Momjian committed
1149
    default to the current time?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1150

Bruce Momjian's avatar
Bruce Momjian committed
1151
    <P>Use <I>CURRENT_TIMESTAMP</I>:</P>
Bruce Momjian's avatar
Bruce Momjian committed
1152
<PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1153
    CREATE TABLE test (x int, modtime timestamp DEFAULT CURRENT_TIMESTAMP );
Bruce Momjian's avatar
Bruce Momjian committed
1154
</PRE>
1155

1156
    <H4><A name="4.18">4.18</A>) How do I perform an outer join?</H4>
1157

1158 1159
    <P>PostgreSQL supports outer joins using the SQL standard syntax.
    Here are two examples:</P>
Bruce Momjian's avatar
Bruce Momjian committed
1160 1161 1162 1163
<PRE>
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1164
    or 
Bruce Momjian's avatar
Bruce Momjian committed
1165 1166 1167 1168
<PRE>
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 USING (col);
</PRE>
Bruce Momjian's avatar
Bruce Momjian committed
1169

Bruce Momjian's avatar
Bruce Momjian committed
1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182
    <P>These identical queries join t1.col to t2.col, and also return
    any unjoined rows in t1 (those with no match in t2). A
    <SMALL>RIGHT</SMALL> join would add unjoined rows of t2. A
    <SMALL>FULL</SMALL> join would return the matched rows plus all
    unjoined rows from t1 and t2. The word <SMALL>OUTER</SMALL> is
    optional and is assumed in <SMALL>LEFT</SMALL>,
    <SMALL>RIGHT</SMALL>, and <SMALL>FULL</SMALL> joins. Ordinary joins
    are called <SMALL>INNER</SMALL> joins.</P>

    <P>In previous releases, outer joins can be simulated using
    <SMALL>UNION</SMALL> and <SMALL>NOT IN</SMALL>. For example, when
    joining <I>tab1</I> and <I>tab2</I>, the following query does an
    <I>outer</I> join of the two tables:<BR>
Bruce Momjian's avatar
Bruce Momjian committed
1183
    <BR>
Bruce Momjian's avatar
Bruce Momjian committed
1184
    </P>
Bruce Momjian's avatar
Bruce Momjian committed
1185 1186 1187 1188 1189 1190 1191 1192
<PRE>
    SELECT tab1.col1, tab2.col2
    FROM tab1, tab2
    WHERE tab1.col1 = tab2.col1
    UNION ALL
    SELECT tab1.col1, NULL
    FROM tab1
    WHERE tab1.col1 NOT IN (SELECT tab2.col1 FROM tab2)
Bruce Momjian's avatar
Bruce Momjian committed
1193
    ORDER BY col1
Bruce Momjian's avatar
Bruce Momjian committed
1194
</PRE>
1195

1196
    <H4><A name="4.19">4.19</A>) How do I perform queries using
Bruce Momjian's avatar
Bruce Momjian committed
1197
    multiple databases?</H4>
Bruce Momjian's avatar
Bruce Momjian committed
1198

1199
    <P>There is no way to query a database other than the current one.
Bruce Momjian's avatar
Bruce Momjian committed
1200 1201
    Because PostgreSQL loads database-specific system catalogs, it is
    uncertain how a cross-database query should even behave.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1202

Bruce Momjian's avatar
Bruce Momjian committed
1203
    <P><I>contrib/dblink</I> allows cross-database queries using
1204 1205 1206
    function calls. Of course, a client can make simultaneous
    connections to different databases and merge the results on the
    client side.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1207

1208
    <H4><A name="4.20">4.20</A>) How do I return multiple rows or
1209 1210
    columns from a function?</H4>

Bruce Momjian's avatar
Bruce Momjian committed
1211 1212 1213 1214
    <P>In 7.3, you can easily return multiple rows or columns from a
    function,
    <a href="http://techdocs.postgresql.org/guides/SetReturningFunctions">
    http://techdocs.postgresql.org/guides/SetReturningFunctions</a>.
1215

1216
    <H4><A name="4.21">4.21</A>) Why can't I reliably create/drop
1217
    temporary tables in PL/PgSQL functions?</H4>
1218
    <P>PL/PgSQL caches function contents, and an unfortunate side effect
1219 1220 1221 1222 1223
    is that if a PL/PgSQL function accesses a temporary table, and that
    table is later dropped and recreated, and the function called
    again, the function will fail because the cached function contents
    still point to the old temporary table. The solution is to use
    <SMALL>EXECUTE</SMALL> for temporary table access in PL/PgSQL. This
1224 1225
    will cause the query to be reparsed every time.</P>

1226
    <H4><A name="4.22">4.22</A>) What encryption options are available?
1227 1228
    </H4>
    <UL>
Bruce Momjian's avatar
Bruce Momjian committed
1229
    <LI><I>contrib/pgcrypto</I> contains many encryption functions for
1230
    use in <SMALL>SQL</SMALL> queries.</LI>
1231 1232 1233 1234 1235 1236 1237 1238 1239
    <LI>To encrypt transmission from the client to the server, the server
    must have the <I>ssl</I> option set to <I>true</I> in <I>postgresql.conf,
    </I> and an applicable <I>host</I> or <I>hostssl</I> record must exist in
    <I>pg_hba.conf</I>, and the client <I>sslmode</I> must not be
    <I>disable.</I> (Note that it is also possible to use a third-party
    encrypted transport, such as stunnel or ssh, rather than PostgreSQL's
    native SSL connections.)
    <LI>Database user passwords are automatically encrypted when stored in
    version 7.3. In previous versions, you must enable the option
Bruce Momjian's avatar
Bruce Momjian committed
1240
    <I>PASSWORD_ENCRYPTION</I> in <I>postgresql.conf</I>.</LI>
Bruce Momjian's avatar
Bruce Momjian committed
1241
    <LI>The server can run using an encrypted file system.</LI>
1242
    </UL>
Bruce Momjian's avatar
Bruce Momjian committed
1243

1244 1245
    <HR>

Bruce Momjian's avatar
Bruce Momjian committed
1246
    <H2 align="center">Extending PostgreSQL</H2>
1247

Bruce Momjian's avatar
Bruce Momjian committed
1248 1249
    <H4><A name="5.1">5.1</A>) I wrote a user-defined function. When I
    run it in <I>psql</I>, why does it dump core?</H4>
1250

Bruce Momjian's avatar
Bruce Momjian committed
1251 1252
    <P>The problem could be a number of things. Try testing your
    user-defined function in a stand-alone test program first.</P>
1253

Bruce Momjian's avatar
Bruce Momjian committed
1254 1255
    <H4><A name="5.2">5.2</A>) How can I contribute some nifty new
    types and functions to PostgreSQL?</H4>
1256

Bruce Momjian's avatar
Bruce Momjian committed
1257 1258 1259
    <P>Send your extensions to the <I>pgsql-hackers</I> mailing list,
    and they will eventually end up in the <I>contrib/</I>
    subdirectory.</P>
1260

Bruce Momjian's avatar
Bruce Momjian committed
1261 1262
    <H4><A name="5.3">5.3</A>) How do I write a C function to return a
    tuple?</H4>
1263

1264 1265 1266 1267
    <P>In versions of PostgreSQL beginning with 7.3, table-returning
    functions are fully supported in C, PL/PgSQL, and SQL. See the
    Programmer's Guide for more information. An example of a
    table-returning function defined in C can be found in
Bruce Momjian's avatar
Bruce Momjian committed
1268
    <I>contrib/tablefunc</I>.</P>
1269

Bruce Momjian's avatar
Bruce Momjian committed
1270 1271
    <H4><A name="5.4">5.4</A>) I have changed a source file. Why does
    the recompile not see the change?</H4>
1272

Bruce Momjian's avatar
Bruce Momjian committed
1273 1274 1275 1276 1277
    <P>The <I>Makefiles</I> do not have the proper dependencies for
    include files. You have to do a <I>make clean</I> and then another
    <I>make</I>. If you are using <SMALL>GCC</SMALL> you can use the
    <I>--enable-depend</I> option of <I>configure</I> to have the
    compiler compute the dependencies automatically.</P>
Bruce Momjian's avatar
Bruce Momjian committed
1278
  </BODY>
1279
</HTML>
Bruce Momjian's avatar
Bruce Momjian committed
1280