FAQ.html

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML>
  <HEAD>
    <META name="generator" content="HTML Tidy, see www.w3.org">
    <META http-equiv="Content-Type" content="text/html; charset=US-ASCII">
    <TITLE>PostgreSQL FAQ</TITLE>
  </HEAD>

  <BODY bgcolor="#ffffff" text="#000000" link="#ff0000" vlink="#a00000"
  alink="#0000ff">
    <H1>Frequently Asked Questions (FAQ) for PostgreSQL</H1>

    <P>Last updated: Fri Apr 29 23:51:45 EDT 2005</P>

    <P>Current maintainer: Bruce Momjian (<A href=
    "mailto:pgman@candle.pha.pa.us">pgman@candle.pha.pa.us</A>)
    </P>

    <P>The most recent version of this document can be viewed at <A href=
    "http://www.postgresql.org/files/documentation/faqs/FAQ.html">
    http://www.postgresql.org/files/documentation/faqs/FAQ.html</A>.</P>

    <P>Platform-specific questions are answered at <A href=
    "http://www.postgresql.org/docs/faq/">
    http://www.postgresql.org/docs/faq/</A>.</P>
    <HR>

    <H2 align="center">General Questions</H2>
    <A href="#1.1">1.1</A>) What is PostgreSQL? How is it pronounced?<BR>
     <A href="#1.2">1.2</A>) What is the copyright of PostgreSQL?<BR>
     <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>) How do I submit a bug report?<BR>
     <A href="#1.7">1.7</A>) What is the latest release?<BR>
     <A href="#1.8">1.8</A>) What documentation is available?<BR>
     <A href="#1.9">1.9</A>) How do I find out about known bugs or
    missing features?<BR>
     <A href="#1.10">1.10</A>) How can I learn <SMALL>SQL</SMALL>?<BR>
     <A href="#1.11">1.11</A>) How do I join the development team?<BR>
     <A href="#1.12">1.12</A>) How does PostgreSQL compare to other
    <SMALL>DBMS</SMALL>s?<BR>
     

    <H2 align="center">User Client Questions</H2>
    <A href="#2.1">2.1</A>) What interfaces are available for
    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>
     

    <H2 align="center">Administrative Questions</H2>
    <A href="#3.1">3.1</A>) How do I install PostgreSQL somewhere other
    than <I>/usr/local/pgsql</I>?<BR>
     <A href="#3.2">3.2</A>) How do I control connections from other
    hosts?<BR>
     <A href="#3.3">3.3</A>) How do I tune the database engine for
    better performance?<BR>
     <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
    clients"</I> when trying to connect?<BR>
     <A href="#3.6">3.6</A>) Why do I need to do a dump and restore
    to upgrade PostgreSQL releases?<BR>
     <A href="#3.7">3.7</A>) What computer hardware should I use?<BR>


    <H2 align="center">Operational Questions</H2>
     <A href="#4.1">4.1</A>) How do I <SMALL>SELECT</SMALL> only the
    first few rows of a query?  A random row?<BR>
     <A href="#4.2">4.2</A>) How do I find out what tables, indexes,
    databases, and users are defined?  How do I see the queries used
    by <I>psql</I> to display them?<BR>
     <A href="#4.3">4.3</A>) How do you change a column's data type?<BR>
     <A href="#4.4">4.4</A>) What is the maximum size for a row, a
    table, and a database?<BR>
     <A href="#4.5">4.5</A>) How much database disk space is required
    to store data from a typical text file?<BR>
     <A href="#4.6">4.6</A>) Why are my queries slow?  Why don't they
    use my indexes?<BR>
     <A href="#4.7">4.7</A>) How do I see how the query optimizer is
    evaluating my query?<BR>
     <A href="#4.8">4.8</A>) How do I perform regular expression
    searches and case-insensitive regular expression searches? How do I
    use an index for case-insensitive searches?<BR>
     <A href="#4.9">4.9</A>) In a query, how do I detect if a field
    is <SMALL>NULL</SMALL>?  How can I sort on whether a field is <SMALL>
    NULL</SMALL> or not?<BR>
     <A href="#4.10">4.10</A>) What is the difference between the
    various character types?<BR>
     <A href="#4.11.1">4.11.1</A>) How do I create a
    serial/auto-incrementing field?<BR>
     <A href="#4.11.2">4.11.2</A>) How do I get the value of a
    <SMALL>SERIAL</SMALL> insert?<BR>
     <A href="#4.11.3">4.11.3</A>) Doesn't <I>currval()</I>
    lead to a race condition with other users?<BR>
     <A href="#4.11.4">4.11.4</A>) Why aren't my sequence numbers
    reused on transaction abort? Why are there gaps in the numbering of
    my sequence/SERIAL column?<BR>
     <A href="#4.12">4.12</A>) What is an <SMALL>OID</SMALL>? What is a
    <SMALL>CTID</SMALL>?<BR>
     <A href="#4.13">4.13</A>) Why do I get the error <I>"ERROR: Memory
    exhausted in AllocSetAlloc()"</I>?<BR>
     <A href="#4.14">4.14</A>) How do I tell what PostgreSQL version I
    am running?<BR>
     <A href="#4.15">4.15</A>) How do I create a column that will
    default to the current time?<BR>
     <A href="#4.16">4.16</A>) How do I perform an outer join?<BR>
     <A href="#4.17">4.17</A>) How do I perform queries using multiple
    databases?<BR>
     <A href="#4.18">4.18</A>) How do I return multiple rows or columns
    from a function?<BR>
     <A href="#4.19">4.19</A>) Why do I get "relation with OID #####
    does not exist" errors when accessing temporary tables in PL/PgSQL
    functions?<BR>
     <A href="#4.20">4.20</A>) What encryption options are available?<BR>
     <A href="#4.21">4.21</A>) What replication solutions are available?<BR>
     

    <HR>

    <H2 align="center">General Questions</H2>

    <H3><A name="1.1">1.1</A>) What is PostgreSQL? How is it pronounced?</H3>

    <P>PostgreSQL is pronounced <I>Post-Gres-Q-L</I>, also called just
    <I>Postgres</I>.</P>

    <P>PostgreSQL is an object-relational database system that has the
    features of traditional commercial database systems with
    enhancements to be found in next-generation <SMALL>DBMS</SMALL>
    systems. PostgreSQL is free and the complete source code is
    available.</P>

    <P>PostgreSQL development is performed by a team of mostly volunteer
    developers spread throughout the world and communicating via the
    Internet. It is a community project and is not controlled by any
    company. To get involved, see the developer's FAQ at <A href=
    "http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html">
    http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html</A>
    </P>

    <H3><A name="1.2">1.2</A>) What is the copyright of
    PostgreSQL?</H3>

    <P>PostgreSQL is distributed under the classic BSD license. It has
    no restrictions on how the source code can be used. We like it and
    have no intention of changing it.</P>

    <P>This is the BSD license we use:</P>

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

    <P>Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
    Portions Copyright (c) 1994-1996 Regents of the University of California</P>

    <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>

    <H3><A name="1.3">1.3</A>) What platforms does PostgreSQL support?</H3>

    <P>In general, any modern Unix-compatible platform should be able to
    run PostgreSQL. The platforms that had received explicit testing at
    the time of release are listed in the installation
    instructions.</P>

    <P>PostgreSQL also 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">
    http://pgfoundry.org/projects/pginstaller</a>.  MSDOS-based versions
    of Windows (Win95, Win98, WinMe) can run PostgreSQL using Cygwin.</P>

    <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=
    "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>

    <H3><A name="1.4">1.4</A>) Where can I get PostgreSQL?</H3>

    <P>Via web browser, use <a href="http://www.postgresql.org/ftp/">
    http://www.postgresql.org/ftp/</a>, and via ftp, use
    <A href="ftp://ftp.PostgreSQL.org/pub/">
    ftp://ftp.PostgreSQL.org/pub/</A>.</P>

    <H3><A name="1.5">1.5</A>) Where can I get support?</H3>

    <P>The PostgreSQL community provides assistance to many of its users
    via email.  The main web site to subscribe to the email lists is
    <a href="http://www.postgresql.org/community/lists/">
    http://www.postgresql.org/community/lists/</a>. The <I>general</I>
    or <I>bugs</I> lists are a good place to start.</P>

    <P>The major IRC channel is <I>#postgresql</I> on Freenode
    (<I>irc.freenode.net</I>).  To connect you can use the Unix
    program <CODE>irc -c '#postgresql' "$USER" irc.freenode.net</CODE>
    or use any other IRC clients.  A Spanish one also exists
    on the same network, (<I>#postgresql-es</I>), and a French one,
    (<I>#postgresqlfr</I>).  There is also a PostgreSQL channel on EFNet.</P>

    <P>A list of commercial support companies is available at <A href=
    "http://techdocs.postgresql.org/companies.php">
    http://techdocs.postgresql.org/companies.php</A>.</P>

    <H3><A name="1.6">1.6</A>) How do I submit a bug report?</H3>

    <P>Visit the PostgreSQL bug form at <A href=
    "http://www.postgresql.org/support/submitbug">
    http://www.postgresql.org/support/submitbug</A>.</P>

    <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.</P>

    <H3><A name="1.7">1.7</A>) What is the latest release?</H3>

    <P>The latest release of PostgreSQL is version 8.0.2.</P>

    <P>We plan to have a major release every year, with minor releases
    every few months.</P>

    <H3><A name="1.8">1.8</A>) What documentation is available?</H3>

    <P>PostgreSQL includes extensive documentation, including a large
    manual, manual pages, and some test examples. See the <I>/doc</I>
    directory. You can also browse the manuals online at <A href=
    "http://www.PostgreSQL.org/docs">http://www.PostgreSQL.org/docs</A>.
    </P>

    <P>There are two PostgreSQL books available online at <A href=
    "http://www.postgresql.org/docs/books/awbook.html">http://www.postgresql.org/docs/books/awbook.html</A>
    and <A href=
    "http://www.commandprompt.com/ppbook/">http://www.commandprompt.com/ppbook/</A>.
    There are a number of PostgreSQL books available for purchase.  One
    of the most popular ones is by Korry Douglas.  A list of book reviews
    can be found at <A href=
    "http://techdocs.postgresql.org/techdocs/bookreviews.php">http://techdocs.PostgreSQL.org/techdocs/bookreviews.php</A>.
    There is also a collection of PostgreSQL technical articles at <A
    href=
    "http://techdocs.PostgreSQL.org/">http://techdocs.PostgreSQL.org/</A>.</P>

    <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>

    <P>Our web site contains even more documentation.</P>

    <H3><A name="1.9">1.9</A>) How do I find out about known bugs or
    missing features?</H3>

    <P>PostgreSQL supports an extended subset of <SMALL>SQL</SMALL>-92.
    See our <A href="http://www.postgresql.org/docs/faqs.TODO.html">TODO</A>
    list for known bugs, missing features, and future plans.</P>

    <H3><A name="1.10">1.10</A>) How can I learn
    <SMALL>SQL</SMALL>?</H3>

    <P>First, consider the PostgreSQL-specific books mentioned above.
    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>.
    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>

    <P>There is also 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=
    "http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM">
    http://ourworld.compuserve.com/homepages/graeme_birchall/HTM_COOK.HTM,</A>
    and at <A href=
    "http://sqlcourse.com/">http://sqlcourse.com.</A></P>

    <H3><A name="1.11">1.11</A>) How do I join the development
    team?</H3>

    <P>See the <a href="http://www.postgresql.org/docs/faqs.FAQ_DEV.html">
    Developer's FAQ</A>.

    <H3><A name="1.12">1.12</A>) How does PostgreSQL compare to other
    <SMALL>DBMS</SMALL>s?</H3>

    <P>There are several ways of measuring software: features,
    performance, reliability, support, and price.</P>

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

      <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>
      <BR>
      </DD>

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

      <DD>PostgreSQL's performance is comparable to other commercial and
      open source databases. It is faster for some things, slower for
      others.  Our performance is usually +/-10% compared to other databases.
      <BR>
      </DD>

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

      <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>
      <BR>
      </DD>

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

      <DD>Our mailing lists provide contact with a large group of developers
      and users to help resolve any problems encountered. While we cannot
      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.
      (See <A href="#1.5">FAQ section 1.5</A>.)<BR>
      <BR>
      </DD>

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

      <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>
      <BR>
      </DD>
    </DL>


    <HR>

    <H2 align="center">User Client Questions</H2>

    <H3><A name="2.1">2.1</A>) What interfaces are available for
    PostgreSQL?</H3>

    <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>

    <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>

    <H3><A name="2.2">2.2</A>) What tools are available for using
    PostgreSQL with Web pages?</H3>

    <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>

    <P>For Web integration, PHP (<A
    href="http://www.php.net">http://www.php.net</A>) is an excellent
    interface.</P>

    <P>For complex cases, many use the Perl and DBD::Pg with CGI.pm or
    mod_perl.</P>

    <H3><A name="2.3">2.3</A>) Does PostgreSQL have a graphical user
    interface?</H3>

    <P>Yes, see <a href="http://techdocs.postgresql.org/guides/GUITools">
    http://techdocs.postgresql.org/guides/GUITools</a> for a
    detailed list.</P>

    <HR>


    <H2 align="center">Administrative Questions</H2>

    <H3><A name="3.1">3.1</A>) How do I install PostgreSQL somewhere
    other than <I>/usr/local/pgsql</I>?</H3>

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

    <H3><A name="3.2">3.2</A>) How do I control connections from other
    hosts?</H3>

    <P>By default, PostgreSQL only allows connections from the local
    machine using Unix domain sockets or TCP/IP connections. Other
    machines will not be able to connect unless you modify
    <I>listen_addresses</I> in the <I>postgresql.conf</I> file, enable
    host-based authentication by modifying the
    <I>$PGDATA/pg_hba.conf</I> file, and restart the server.</P>

    <H3><A name="3.3">3.3</A>) How do I tune the database engine for
    better performance?</H3>

    <P>There are three major areas for potential performance
    improvement:</P>
    
    <DL>
    <DT><B>Query Changes</B></DT>

    <DD>This involves modifying queries to obtain better
    performance:
    <ul>
    <li>Creation of indexes, including expression and partial
    indexes</li>
    <li>Use of COPY instead of multiple <SMALL>INSERT</SMALL>s</li>
    <li>Grouping of multiple statements into a single transaction to
    reduce commit overhead</li>
    <li>Use of <SMALL>CLUSTER</SMALL> when retrieving many rows from an
    index</li>
    <li>Use of <SMALL>LIMIT</SMALL> for returning a subset of a query's
    output</li>
    <li>Use of Prepared queries</li>
    <li>Use of <SMALL>ANALYZE</SMALL> to maintain accurate optimizer
    statistics</li>
    <li>Regular use of <SMALL>VACUUM</SMALL> or <I>pg_autovacuum</I></li>
    <li>Dropping of indexes during large data changes</li>
    </ul><BR>
    <BR>
    </DD>

    <DT><B>Server Configuration</B></DT>

    <DD>A number of <I>postgresql.conf</I> settings affect performance.
    For more details, see <a href=
    "http://www.postgresql.org/docs/current/static/runtime.html">
    Administration Guide/Server Run-time Environment/Run-time
    Configuration</a> for a full listing, and for commentary see <a
    href="http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html">
    http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html</a>
    and <a href="http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html">
    http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html</a>.
    <BR>
    <BR>
    </DD>

    <DT><B>Hardware Selection</B></DT>

    <DD>The effect of hardware on performance is detailed in <a
    href="http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html">
    http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html</a> and
    <a href="http://www.powerpostgresql.com/PerfList/">
    http://www.powerpostgresql.com/PerfList/</a>.
    <BR>
    <BR>
    </DD>
    </DL>

    <H3><A name="3.4">3.4</A>) What debugging features are
    available?</H3>

    <P>There are many <CODE>log_*</CODE> server configuration variables
    that enable printing of query and process statistics which can be
    very useful for debugging and performance measurements.</P>

    <H3><A name="3.5">3.5</A>) Why do I get <I>"Sorry, too many
    clients"</I> when trying to connect?</H3>

    <P>You have reached the default limit is 100 database sessions. You
    need to increase the <I>postmaster</I>'s limit on how many
    concurrent backend processes it can start by changing the
    <I>max_connections</I> value in <I>postgresql.conf</I> and
    restarting the <I>postmaster</I>.</P>

    <H3><A name="3.6">3.6</A>) Why do I need to do a dump and restore
    to upgrade between major PostgreSQL releases?</H3>

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

    <H3><A name="3.7">3.7</A>) What computer hardware should I use?</H3>

    <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
    less expensive hardware.  PostgreSQL will run on almost any hardware,
    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>

    <HR>

    <H2 align="center">Operational Questions</H2>

    <H3><A name="4.1">4.1</A>) How do I <SMALL>SELECT</SMALL> only the
    first few rows of a query?  A random row?</H3>

    <P>To retrieve only a few rows, if you know at the number of rows
    needed at the time of the <SMALL>SELECT</SMALL> use
    <SMALL>LIMIT</SMALL> . If an index matches the <SMALL>ORDER
    BY</SMALL> it is possible the entire query does not have to be
    executed. If you don't know the number of rows at
    <SMALL>SELECT</SMALL> time, use a cursor and
    <SMALL>FETCH</SMALL>.</P>

    <P>To <SMALL>SELECT</SMALL> a random row, use:</P>
<PRE>
    SELECT col
    FROM tab
    ORDER BY random()
    LIMIT 1;
</PRE>

    <H3><A name="4.2">4.2</A>) How do I find out what tables, indexes,
    databases, and users are defined?  How do I see the queries used
    by <I>psql</I> to display them?</H3>

    <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 
    commands you give. PostgreSQL also provides an <SMALL>SQL</SMALL> compliant
    INFORMATION SCHEMA interface you can query to get information about the
    database.</P>

    <P>There are also system tables beginning with <I>pg_</I> that describe
    these too.</P>
    
    <P>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>

    <H3><A name="4.3">4.3</A>) How do you change a column's data type?</H3>

    <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>

    <P>In earlier releases, do this:</P>
<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>);
    ALTER TABLE tab DROP COLUMN old_col;
    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>
    
    <H3><A name="4.4">4.4</A>) What is the maximum size for a row, a
    table, and a database?</H3>

    <P>These are the limits:</P>
<BLOCKQUOTE>
<TABLE>
<TR><TD>Maximum size for a database?</TD><TD>unlimited (32 TB databases
exist)</TD></TR>
<TR><TD>Maximum size for a table?</TD><TD>32 TB</TD></TR>
<TR><TD>Maximum size for a row?</TD><TD>1.6TB</TD></TR>
<TR><TD>Maximum size for a field?</TD><TD>1 GB</TD></TR>
<TR><TD>Maximum number of rows in a table?</TD><TD>unlimited</TD></TR>
<TR><TD>Maximum number of columns in a table?</TD><TD>250-1600 depending
on column types</TD></TR>
<TR><TD>Maximum number of indexes on a
table?</TD><TD>unlimited</TD></TR>
</TABLE>
</BLOCKQUOTE>

    <P>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.</P>

    <P>The maximum table size of 32 TB does not require large file
    support from the operating system. Large tables are stored as
    multiple 1 GB files so file system size limits are not
    important.</P>

    <P>The maximum table size and maximum number of columns can be
    quadrupled by increasing the default block size to 32k.</P>

    <H3><A name="4.5">4.5</A>) How much database disk space is required
    to store data from a typical text file?</H3>

    <P>A PostgreSQL database may require up to five times the disk
    space to store data from a text file.</P>

    <P>As an example, consider a file of 100,000 lines with an integer
    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>
<PRE>
    32 bytes: each row header (approximate)
    24 bytes: one int field and one text field
   + 4 bytes: pointer on page to tuple
   ----------------------------------------
    60 bytes per row

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

   8192 bytes per page
   -------------------   =  136 rows per database page (rounded down)
     60 bytes per row

   100000 data rows
   --------------------  =  735 database pages (rounded up)
      128 rows per page

735 database pages * 8192 bytes per page  =  6,021,120 bytes (6 MB)
</PRE>

    <P>Indexes do not require as much overhead, but do contain the data
    that is being indexed, so they can be large also.</P>

    <P><SMALL>NULL</SMALL>s are stored as bitmaps, so they
    use very little space.</P>
    
    <H3><A name="4.6">4.6</A>) Why are my queries slow?  Why don't they
    use my indexes?</H3>

    <P>Indexes are not used by every query. Indexes are used only if the
    table is larger than a minimum size, and the query selects only a
    small percentage of the rows in the table. This is because the random
    disk access caused by an index scan can be slower than a straight read
    through the table, or sequential scan. </P>

    <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
    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
    ORDER BY col [ DESC ]
    LIMIT 1;
</PRE>

    <P>If you believe the optimizer is incorrect in choosing a
    sequential scan, use <CODE>SET enable_seqscan TO 'off'</CODE> and
    run query again to see if an index scan is indeed faster.</P>

    <P>When using wild-card operators such as <SMALL>LIKE</SMALL> or
    <I>~</I>, indexes can only be used in certain circumstances:</P>
    <UL>
    <LI>The beginning of the search string must be anchored to the start
    of the string, i.e.
    <UL>
    <LI><SMALL>LIKE</SMALL> patterns must not start with <I>%</I>.</LI>
    <LI><I>~</I> (regular expression) patterns must start with
    <I>^</I>.</LI>
    </UL></LI>
    <LI>The search string can not start with a character class,
    e.g. [a-e].</LI>
    <LI>Case-insensitive searches such as <SMALL>ILIKE</SMALL> and
    <I>~*</I> do not utilize indexes. Instead, use expression
    indexes, which are described in section <a href="#4.8">4.8</a>.</LI>
    <LI>The default <I>C</I> locale must be used during
    <i>initdb</i> because it is not possible to know the next-greatest
    character in a non-C locale.  You can create a special
    <CODE>text_pattern_ops</CODE> index for such cases that work only
    for <SMALL>LIKE</SMALL> indexing.
    </LI>
    </UL>

    <P>In pre-8.0 releases, indexes often can not be used unless the data
    types exactly match the index's column types.  This was particularly
    true of int2, int8, and numeric column indexes.</P>

    <H3><A name="4.7">4.7</A>) How do I see how the query optimizer is
    evaluating my query?</H3>

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

    <H3><A name="4.8">4.8</A>) How do I perform regular expression
    searches and case-insensitive regular expression searches? How do I
    use an index for case-insensitive searches?</H3>

    <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
    <SMALL>ILIKE</SMALL>.</P>

    <P>Case-insensitive equality comparisons are normally expressed
    as:</P>
<PRE>
    SELECT *
    FROM tab
    WHERE lower(col) = 'abc';
</PRE>
    This will not use an standard index. However, if you create a
    expresssion index, it will be used: 
<PRE>
    CREATE INDEX tabindex ON tab (lower(col));
</PRE>

    <H3><A name="4.9">4.9</A>) In a query, how do I detect if a field
    is <SMALL>NULL</SMALL>?  How can I sort on whether a field is <SMALL>
    NULL</SMALL> or not?</H3>

    <P>You test the column with <SMALL>IS NULL</SMALL> and <SMALL>IS
    NOT NULL</SMALL>, like this:</P>

<PRE>
   SELECT *
   FROM tab
   WHERE col IS NULL;
</PRE>

   <P>To sort by the <SMALL>NULL</SMALL> status, use the <SMALL>IS NULL</SMALL>
   and <SMALL>IS NOT NULL</SMALL> modifiers in your <SMALL>ORDER BY</SMALL> clause.
   Things that are <I>true</I> will sort higher than things that are <I>false</I>,
   so the following will put NULL entries at the top of the resulting list:</P>

<PRE>
   SELECT *
   FROM tab
   ORDER BY (col IS NOT NULL)
</PRE>

    <H3><A name="4.10">4.10</A>) What is the difference between the
    various character types?</H3>
<BLOCKQUOTE>
<TABLE>
<TR><TH>Type</TH><TH>Internal Name</TH><TH>Notes</TH></TR>
<TR><TD>VARCHAR(n)</TD><TD>varchar</TD><TD>size specifies maximum
length, no padding</TD></TR>
<TR><TD>CHAR(n)</TD><TD>bpchar</TD><TD>blank padded to the specified
fixed length</TD></TR>
<TR><TD>TEXT</TD><TD>text</TD><TD>no specific upper limit on
length</TD></TR>
<TR><TD>BYTEA</TD><TD>bytea</TD><TD>variable-length byte array
(null-byte safe)</TD></TR>
<TR><TD>"char"</TD><TD>char</TD><TD>one character</TD></TR>
</TABLE>
</BLOCKQUOTE>

    <P>You will see the internal name when examining system catalogs
    and in some error messages.</P>

    <P>The first four types above are "varlena" types (i.e., the first
    four bytes on disk are the length, followed by the data). Thus the
    actual space used is slightly greater than the declared size.
    However, long values are also subject to compression, so the space
    on disk might also be less than expected.</P>

    <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,
    particularly values that include <SMALL>NULL</SMALL> bytes. All the
    types described here have similar performance characteristics.</P>

    <H3><A name="4.11.1">4.11.1</A>) How do I create a
    serial/auto-incrementing field?</H3>

    <P>PostgreSQL supports a <SMALL>SERIAL</SMALL> data type. It
    auto-creates a sequence.  For example, this:</P>
<PRE>
    CREATE TABLE person ( 
        id   SERIAL, 
        name TEXT 
    );
</PRE>

    is automatically translated into this: 

<PRE>
    CREATE SEQUENCE person_id_seq;
    CREATE TABLE person ( 
        id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),
        name TEXT
    );
</PRE>

    See the <I>create_sequence</I> manual page for more information
    about sequences.

    <H3><A name="4.11.2">4.11.2</A>) How do I get the value of a
    <SMALL>SERIAL</SMALL> insert?</H3>

    <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
    example table in <A href="#4.11.1">4.11.1</A>, an example in a
    pseudo-language would look like this:</P>
<PRE>
    new_id = execute("SELECT nextval('person_id_seq')");
    execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')");
</PRE>

    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 and your <SMALL>SERIAL</SMALL> column,
    respectively.

    <P>Alternatively, you could retrieve the assigned <SMALL>SERIAL</SMALL>
    value with the <I>currval()</I> function <I>after</I> it was inserted by
    default, e.g.,</P>
<PRE>
    execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
    new_id = execute("SELECT currval('person_id_seq')");
</PRE>
    
    <H3><A name="4.11.3">4.11.3</A>) Doesn't <I>currval()</I>
    lead to a race condition with other users?</H3>

    <P>No. <I>currval()</I> returns the current value assigned by your
    session, not by all sessions.</P>

    <H3><A name="4.11.4">4.11.4</A>) Why aren't my sequence numbers
    reused on transaction abort? Why are there gaps in the numbering of
    my sequence/SERIAL column?</H3>

    <P>To improve concurrency, sequence values are given out to running
    transactions as needed and are not locked until the transaction
    completes. This causes gaps in numbering from aborted
    transactions.</P>

    <H3><A name="4.12">4.12</A>) What is an <SMALL>OID</SMALL>? What is
    a <SMALL>CTID</SMALL>?</H3>

    <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
    <SMALL>SERIAL</SMALL> rather than O<SMALL>ID</SMALL>s because
    <SMALL>SERIAL</SMALL> sequences are unique only within a single
    table. and are therefore less likely to overflow.
    <SMALL>SERIAL8</SMALL> is available for storing eight-byte sequence
    values.</P>

    <P>C<SMALL>TID</SMALL>s are used to identify specific physical rows
    with block and offset values. C<SMALL>TID</SMALL>s change after rows
    are modified or reloaded. They are used by index entries to point
    to physical rows.</P>

    <H3><A name="4.13">4.13</A>) Why do I get the error <I>"ERROR:
    Memory exhausted in AllocSetAlloc()"</I>?</H3>

    <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>
<PRE>
    ulimit -d 262144
    limit datasize 256m
</PRE>

    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
    client.

    <H3><A name="4.14">4.14</A>) How do I tell what PostgreSQL version
    I am running?</H3>

    <P>From <I>psql</I>, type <CODE>SELECT version();</CODE></P>

    <H3><A name="4.15">4.15</A>) How do I create a column that will
    default to the current time?</H3>

    <P>Use <I>CURRENT_TIMESTAMP</I>:</P>
<PRE>
    CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
</PRE>

    <H3><A name="4.16">4.16</A>) How do I perform an outer join?</H3>

    <P>PostgreSQL supports outer joins using the SQL standard syntax.
    Here are two examples:</P>
<PRE>
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
</PRE>
    or 
<PRE>
    SELECT *
    FROM t1 LEFT OUTER JOIN t2 USING (col);
</PRE>

    <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>

    <H3><A name="4.17">4.17</A>) How do I perform queries using
    multiple databases?</H3>

    <P>There is no way to query a database other than the current one.
    Because PostgreSQL loads database-specific system catalogs, it is
    uncertain how a cross-database query should even behave.</P>

    <P><I>contrib/dblink</I> allows cross-database queries using
    function calls. Of course, a client can also make simultaneous
    connections to different databases and merge the results on the
    client side.</P>

    <H3><A name="4.18">4.18</A>) How do I return multiple rows or
    columns from a function?</H3>

    <P>It is easy using set-returning functions, 
    <a href="http://techdocs.postgresql.org/guides/SetReturningFunctions">
    http://techdocs.postgresql.org/guides/SetReturningFunctions</a></P>.

    <H3><A name="4.19">4.19</A>) Why do I get "relation with OID #####
    does not exist" errors when accessing temporary tables in PL/PgSQL
    functions?</H3>

    <P>PL/PgSQL caches function scripts, and an unfortunate side effect
    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
    will cause the query to be reparsed every time.</P>

    <H3><A name="4.20">4.20</A>) What encryption options are available?
    </H3>
    <UL>
    <LI><I>contrib/pgcrypto</I> contains many encryption functions for
    use in <SMALL>SQL</SMALL> queries.</LI>
    <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>
    <LI>Database user passwords are automatically encrypted when stored in
    the system tables.</LI>
    <LI>The server can also run using an encrypted file system.</LI>
    </UL>

    <H3><A name="4.21">4.21</A>) What replication solutions are available?
    </H3>
    <P>Though "replication" is a single term, there are several technologies
    for doing replication, with advantages and disadvantages for each.</P>

    <P>Master/slave replication allows a single master to receive read/write
    queries, while slaves can only accept read/<SMALL>SELECT</SMALL> queries.
    The most popular freely available master-slave PostgreSQL replication
    solution is <A href="http://gborg.postgresql.org/project/slony1/projdisplay.php">
    Slony-I</A>.</P>

    <P>Multi-master replication allows read/write queries to be sent to
    multiple replicated computers.  This capability also has a severe
    impact on performance due to the need to synchronize changes between
    servers.  <A href="http://pgfoundry.org/projects/pgcluster/">Pgcluster</a>
    is the most popular such solution freely available for PostgreSQL.</P>

    <P>There are also commercial and hardware-based replication solutions
    available supporting a variety of replication models.</P>
  </BODY>
</HTML>