insert.sgml

PostgreSQL7.4.6 for Linux

<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v 1.25 2003/09/12 00:12:47 tgl Exp $
PostgreSQL documentation
-->

<refentry id="SQL-INSERT">
 <refmeta>
  <refentrytitle id="SQL-INSERT-TITLE">INSERT</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>

 <refnamediv>
  <refname>INSERT</refname>
  <refpurpose>create new rows in a table</refpurpose>
 </refnamediv>

 <indexterm zone="sql-insert">
  <primary>INSERT</primary>
 </indexterm>

 <refsynopsisdiv>
<synopsis>
INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
    { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> }
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>INSERT</command> allows one to insert new rows into a
   table. One can insert
   a single row at a time or several rows as a result of a query.
  </para>

  <para>
   The columns in the target list may be listed in any order.
   Each column not present in the target list will be inserted
   using a default value, either its declared default value
   or null.
  </para>

  <para>
   If the expression for each column is not of the correct data type,
   automatic type conversion will be attempted.
  </para>

  <para>
   You must have <literal>INSERT</literal> privilege to a table in
   order to insert into it.  If you use the <replaceable
   class="PARAMETER">query</replaceable> clause to insert rows from a
   query, you also need to have <literal>SELECT</literal> privilege on
   any table used in the query.
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>

  <variablelist>
   <varlistentry>
    <term><replaceable class="PARAMETER">table</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of an existing table.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="PARAMETER">column</replaceable></term>
    <listitem>
     <para>
      The name of a column in <replaceable class="PARAMETER">table</replaceable>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>DEFAULT VALUES</literal></term>
    <listitem>
     <para>
      All columns will be filled with their default values.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="PARAMETER">expression</replaceable></term>
    <listitem>
     <para>
      An expression or value to assign to <replaceable
      class="PARAMETER">column</replaceable>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><literal>DEFAULT</literal></term>
    <listitem>
     <para>
      This column will be filled with its default value.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="PARAMETER">query</replaceable></term>
    <listitem>
     <para>
      A query (<command>SELECT</command> statement) that supplies the
      rows to be inserted.  Refer to the <command>SELECT</command>
      statement for a description of the syntax.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Outputs</title>

  <para>
   On successful completion, an <command>INSERT</> command returns a command
   tag of the form
<screen>
INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
</screen>
   The <replaceable class="parameter">count</replaceable> is the number
   of rows inserted.  If <replaceable class="parameter">count</replaceable>
   is exactly one, and the target table has OIDs, then
   <replaceable class="parameter">oid</replaceable> is the
   <acronym>OID</acronym> assigned to the inserted row.  Otherwise
   <replaceable class="parameter">oid</replaceable> is zero.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   Insert a single row into table <literal>films</literal>:

<programlisting>
INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
</programlisting>
  </para>

  <para>
   In this second example, the last column <literal>len</literal> is
   omitted and therefore it will have the default value of null:

<programlisting>
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
</programlisting>
  </para>

  <para>
   The third example uses the <literal>DEFAULT</literal> clause for
   the date columns rather than specifying a value:

<programlisting>
INSERT INTO films VALUES
    ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films (code, title, did, date_prod, kind)
    VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
</programlisting>
  </para>

  <para>
   This examples inserts several rows into table
   <literal>films</literal> from table <literal>tmp</literal>:

<programlisting>
INSERT INTO films SELECT * FROM tmp;
</programlisting>
  </para>

  <para>
   This example inserts into array columns:

<programlisting>
-- Create an empty 3x3 gameboard for noughts-and-crosses
-- (all of these commands create the same board)
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1,'{{"","",""},{},{"",""}}');
INSERT INTO tictactoe (game, board[3][3])
    VALUES (2,'{}');
INSERT INTO tictactoe (game, board)
    VALUES (3,'{{,,},{,,},{,,}}');
</programlisting>
  </para>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   <command>INSERT</command> conforms fully to the SQL standard.
   Possible limitations of the <replaceable
   class="PARAMETER">query</replaceable> clause are documented under
   <xref linkend="sql-select" endterm="sql-select-title">.
  </para>
 </refsect1>
</refentry>

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