ddl.sgml

PostgreSQL7.4.6 for Linux

<!-- $Header: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v 1.21.2.2 2003/11/05 00:05:37 tgl Exp $ -->

<chapter id="ddl">
 <title>Data Definition</title>

 <para>
  This chapter covers how one creates the database structures that
  will hold one's data.  In a relational database, the raw data is
  stored in tables, so the majority of this chapter is devoted to
  explaining how tables are created and modified and what features are
  available to control what data is stored in the tables.
  Subsequently, we discuss how tables can be organized into
  schemas, and how privileges can be assigned to tables.  Finally,
  we will briefly look at other features that affect the data storage,
  such as views, functions, and triggers.
 </para>

 <sect1 id="ddl-basics">
  <title>Table Basics</title>

  <indexterm zone="ddl-basics">
   <primary>table</primary>
  </indexterm>

  <indexterm>
   <primary>row</primary>
  </indexterm>

  <indexterm>
   <primary>column</primary>
  </indexterm>

  <para>
   A table in a relational database is much like a table on paper: It
   consists of rows and columns.  The number and order of the columns
   is fixed, and each column has a name.  The number of rows is
   variable -- it reflects how much data is stored at a given moment.
   SQL does not make any guarantees about the order of the rows in a
   table.  When a table is read, the rows will appear in random order,
   unless sorting is explicitly requested.  This is covered in <xref
   linkend="queries">.  Furthermore, SQL does not assign unique
   identifiers to rows, so it is possible to have several completely
   identical rows in a table.  This is a consequence of the
   mathematical model that underlies SQL but is usually not desirable.
   Later in this chapter we will see how to deal with this issue.
  </para>

  <para>
   Each column has a data type.  The data type constrains the set of
   possible values that can be assigned to a column and assigns
   semantics to the data stored in the column so that it can be used
   for computations.  For instance, a column declared to be of a
   numerical type will not accept arbitrary text strings, and the data
   stored in such a column can be used for mathematical computations.
   By contrast, a column declared to be of a character string type
   will accept almost any kind of data but it does not lend itself to
   mathematical calculations, although other operations such as string
   concatenation are available.
  </para>

  <para>
   <productname>PostgreSQL</productname> includes a sizable set of
   built-in data types that fit many applications.  Users can also
   define their own data types.  Most built-in data types have obvious
   names and semantics, so we defer a detailed explanation to <xref
   linkend="datatype">.  Some of the frequently used data types are
   <type>integer</type> for whole numbers, <type>numeric</type> for
   possibly fractional numbers, <type>text</type> for character
   strings, <type>date</type> for dates, <type>time</type> for
   time-of-day values, and <type>timestamp</type> for values
   containing both date and time.
  </para>

  <indexterm>
   <primary>table</primary>
   <secondary>creating</secondary>
  </indexterm>

  <para>
   To create a table, you use the aptly named <literal>CREATE
   TABLE</literal> command.  In this command you specify at least a
   name for the new table, the names of the columns and the data type
   of each column.  For example:
<programlisting>
CREATE TABLE my_first_table (
    first_column text,
    second_column integer
);
</programlisting>
   This creates a table named <literal>my_first_table</literal> with
   two columns.  The first column is named
   <literal>first_column</literal> and has a data type of
   <type>text</type>; the second column has the name
   <literal>second_column</literal> and the type <type>integer</type>.
   The table and column names follow the identifier syntax explained
   in <xref linkend="sql-syntax-identifiers">.  The type names are
   usually also identifiers, but there are some exceptions.  Note that the
   column list is comma-separated and surrounded by parentheses.
  </para>

  <para>
   Of course, the previous example was heavily contrived.  Normally,
   you would give names to your tables and columns that convey what
   kind of data they store.  So let's look at a more realistic
   example:
<programlisting>
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);
</programlisting>
   (The <type>numeric</type> type can store fractional components, as
   would be typical of monetary amounts.)
  </para>

  <tip>
   <para>
    When you create many interrelated tables it is wise to choose a
    consistent naming pattern for the tables and columns.  For
    instance, there is a choice of using singular or plural nouns for
    table names, both of which are favored by some theorist or other.
   </para>
  </tip>

  <para>
   There is a limit on how many columns a table can contain.
   Depending on the column types, it is between 250 and 1600.
   However, defining a table with anywhere near this many columns is
   highly unusual and often a questionable design.
  </para>

  <indexterm>
   <primary>table</primary>
   <secondary>removing</secondary>
  </indexterm>

  <para>
   If you no longer need a table, you can remove it using the
   <command>DROP TABLE</command> command.  For example:
<programlisting>
DROP TABLE my_first_table;
DROP TABLE products;
</programlisting>
   Attempting to drop a table that does not exist is an error.
   Nevertheless, it is common in SQL script files to unconditionally
   try to drop each table before creating it, ignoring the error
   messages.
  </para>

  <para>
   If you need to modify a table that already exists look into <xref
   linkend="ddl-alter"> later in this chapter.
  </para>

  <para>
   With the tools discussed so far you can create fully functional
   tables.  The remainder of this chapter is concerned with adding
   features to the table definition to ensure data integrity,
   security, or convenience.  If you are eager to fill your tables with
   data now you can skip ahead to <xref linkend="dml"> and read the
   rest of this chapter later.
  </para>
 </sect1>

 <sect1 id="ddl-system-columns">
  <title>System Columns</title>

  <para>
   Every table has several <firstterm>system columns</> that are
   implicitly defined by the system.  Therefore, these names cannot be
   used as names of user-defined columns.  (Note that these
   restrictions are separate from whether the name is a key word or
   not; quoting a name will not allow you to escape these
   restrictions.)  You do not really need to be concerned about these
   columns, just know they exist.
  </para>

  <indexterm>
   <primary>column</primary>
   <secondary>system column</secondary>
  </indexterm>

  <variablelist>
   <varlistentry>
    <term><structfield>oid</></term>
    <listitem>
     <para>
      <indexterm>
       <primary>OID</primary>
       <secondary>column</secondary>
      </indexterm>
      The object identifier (object ID) of a row.  This is a serial
      number that is automatically added by
      <productname>PostgreSQL</productname> to all table rows (unless
      the table was created using <literal>WITHOUT OIDS</literal>, in which
      case this column is not present).  This column is of type
      <type>oid</type> (same name as the column); see <xref
      linkend="datatype-oid"> for more information about the type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>tableoid</></term>
    <listitem>
     <indexterm>
      <primary>tableoid</primary>
     </indexterm>

     <para>
      The OID of the table containing this row.  This column is
      particularly handy for queries that select from inheritance
      hierarchies, since without it, it's difficult to tell which
      individual table a row came from.  The
      <structfield>tableoid</structfield> can be joined against the
      <structfield>oid</structfield> column of
      <structname>pg_class</structname> to obtain the table name.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>xmin</></term>
    <listitem>
     <indexterm>
      <primary>xmin</primary>
     </indexterm>

     <para>
      The identity (transaction ID) of the inserting transaction for
      this row version.  (A row version is an individual state of a
      row; each update of a row creates a new row version for the same
      logical row.)
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>cmin</></term>
    <listitem>
     <indexterm>
      <primary>cmin</primary>
     </indexterm>

     <para>
      The command identifier (starting at zero) within the inserting
      transaction.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>xmax</></term>
    <listitem>
     <indexterm>
      <primary>xmax</primary>
     </indexterm>

     <para>
      The identity (transaction ID) of the deleting transaction, or
      zero for an undeleted row version.  It is possible for this column to
      be nonzero in a visible row version: That usually indicates that the
      deleting transaction hasn't committed yet, or that an attempted
      deletion was rolled back.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>cmax</></term>
    <listitem>
     <indexterm>
      <primary>cmax</primary>
     </indexterm>

     <para>
      The command identifier within the deleting transaction, or zero.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><structfield>ctid</></term>
    <listitem>
     <indexterm>
      <primary>ctid</primary>
     </indexterm>

     <para>
      The physical location of the row version within its table.  Note that
      although the <structfield>ctid</structfield> can be used to
      locate the row version very quickly, a row's
      <structfield>ctid</structfield> will change each time it is
      updated or moved by <command>VACUUM FULL</>.  Therefore
      <structfield>ctid</structfield> is useless as a long-term row
      identifier.  The OID, or even better a user-defined serial
      number, should be used to identify logical rows.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>

   <para>
    OIDs are 32-bit quantities and are assigned from a single cluster-wide
    counter.  In a large or long-lived database, it is possible for the
    counter to wrap around.  Hence, it is bad practice to assume that OIDs
    are unique, unless you take steps to ensure that they are unique.
    Recommended practice when using OIDs for row identification is to create
    a unique constraint on the OID column of each table for which the OID will
    be used.  Never assume that OIDs are unique across tables; use the
    combination of <structfield>tableoid</> and row OID if you need a
    database-wide identifier.  (Future releases of
    <productname>PostgreSQL</productname> are likely to use a separate
    OID counter for each table, so that <structfield>tableoid</>
    <emphasis>must</> be included to arrive at a globally unique identifier.)
   </para>

   <para>
    Transaction identifiers are also 32-bit quantities.  In a
    long-lived database it is possible for transaction IDs to wrap
    around.  This is not a fatal problem given appropriate maintenance
    procedures; see <xref linkend="maintenance"> for details.  It is
    unwise, however, to depend on the uniqueness of transaction IDs
    over the long term (more than one billion transactions).
   </para>

   <para>
    Command
    identifiers are also 32-bit quantities.  This creates a hard limit
    of 2<superscript>32</> (4 billion) <acronym>SQL</acronym> commands
    within a single transaction.  In practice this limit is not a
    problem --- note that the limit is on number of
    <acronym>SQL</acronym> commands, not number of rows processed.
   </para>
 </sect1>

 <sect1 id="ddl-default">
  <title>Default Values</title>

  <indexterm zone="ddl-default">
   <primary>default value</primary>
  </indexterm>

  <para>
   A column can be assigned a default value.  When a new row is
   created and no values are specified for some of the columns, the
   columns will be filled with their respective default values.  A
   data manipulation command can also request explicitly that a column
   be set to its default value, without knowing what this value is.
   (Details about data manipulation commands are in <xref linkend="dml">.)
  </para>

  <para>
   <indexterm><primary>null value</primary><secondary>default value</secondary></indexterm>
   If no default value is declared explicitly, the null value is the
   default value.  This usually makes sense because a null value can
   be thought to represent unknown data.
  </para>

  <para>
   In a table definition, default values are listed after the column
   data type.  For example:
<programlisting>
CREATE TABLE products (
    product_no integer,
    name text,
    price numeric <emphasis>DEFAULT 9.99</emphasis>
);
</programlisting>
  </para>

  <para>
   The default value may be a scalar expression, which will be
   evaluated whenever the default value is inserted
   (<emphasis>not</emphasis> when the table is created).
  </para>
 </sect1>

 <sect1 id="ddl-constraints">
  <title>Constraints</title>

  <indexterm zone="ddl-constraints">
   <primary>constraint</primary>
  </indexterm>

  <para>
   Data types are a way to limit the kind of data that can be stored
   in a table.  For many applications, however, the constraint they
   provide is too coarse.  For example, a column containing a product
   price should probably only accept positive values.  But there is no
   data type that accepts only positive numbers.  Another issue is
   that you might want to constrain column data with respect to other
   columns or rows.  For example, in a table containing product
   information, there should only be one row for each product number.
  </para>

  <para>
   To that end, SQL allows you to define constraints on columns and
   tables.  Constraints give you as much control over the data in your