doc/src/sgml/ref/insert.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-INSERT">
   7  <refmeta>
   8   <refentrytitle id="SQL-INSERT-TITLE">INSERT</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>INSERT</refname>
  15   <refpurpose>create new rows in a table</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-insert">
  19   <primary>INSERT</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
  25     { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
  26     [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
  27 </synopsis>
  28  </refsynopsisdiv>
  29
  30  <refsect1>
  31   <title>Description</title>
  32
  33   <para>
  34    <command>INSERT</command> inserts new rows into a table.
  35    One can insert one or more rows specified by value expressions,
  36    or zero or more rows resulting from a query.
  37   </para>
  38
  39   <para>
  40    The target column names can be listed in any order.  If no list of
  41    column names is given at all, the default is all the columns of the
  42    table in their declared order; or the first <replaceable>N</> column
  43    names, if there are only <replaceable>N</> columns supplied by the
  44    <literal>VALUES</> clause or <replaceable>query</>.  The values
  45    supplied by the <literal>VALUES</> clause or <replaceable>query</> are
  46    associated with the explicit or implicit column list left-to-right.
  47   </para>
  48
  49   <para>
  50    Each column not present in the explicit or implicit column list will be
  51    filled with a default value, either its declared default value
  52    or null if there is none.
  53   </para>
  54
  55   <para>
  56    If the expression for any column is not of the correct data type,
  57    automatic type conversion will be attempted.
  58   </para>
  59
  60   <para>
  61    The optional <literal>RETURNING</> clause causes <command>INSERT</>
  62    to compute and return value(s) based on each row actually inserted.
  63    This is primarily useful for obtaining values that were supplied by
  64    defaults, such as a serial sequence number.  However, any expression
  65    using the table's columns is allowed.  The syntax of the
  66    <literal>RETURNING</> list is identical to that of the output list
  67    of <command>SELECT</>.
  68   </para>
  69
  70   <para>
  71    You must have <literal>INSERT</literal> privilege on a table in
  72    order to insert into it.  If a column list is specified, you only
  73    need <literal>INSERT</literal> privilege on the listed columns.
  74    Use of the <literal>RETURNING</> clause requires <literal>SELECT</>
  75    privilege on all columns mentioned in <literal>RETURNING</>.
  76    If you use the <replaceable
  77    class="PARAMETER">query</replaceable> clause to insert rows from a
  78    query, you of course need to have <literal>SELECT</literal> privilege on
  79    any table or column used in the query.
  80   </para>
  81  </refsect1>
  82
  83  <refsect1>
  84   <title>Parameters</title>
  85
  86   <variablelist>
  87    <varlistentry>
  88     <term><replaceable class="PARAMETER">table</replaceable></term>
  89     <listitem>
  90      <para>
  91       The name (optionally schema-qualified) of an existing table.
  92      </para>
  93     </listitem>
  94    </varlistentry>
  95
  96    <varlistentry>
  97     <term><replaceable class="PARAMETER">column</replaceable></term>
  98     <listitem>
  99      <para>
 100       The name of a column in <replaceable class="PARAMETER">table</replaceable>.
 101       The column name can be qualified with a subfield name or array
 102       subscript, if needed.  (Inserting into only some fields of a
 103       composite column leaves the other fields null.)
 104      </para>
 105     </listitem>
 106    </varlistentry>
 107
 108    <varlistentry>
 109     <term><literal>DEFAULT VALUES</literal></term>
 110     <listitem>
 111      <para>
 112       All columns will be filled with their default values.
 113      </para>
 114     </listitem>
 115    </varlistentry>
 116
 117    <varlistentry>
 118     <term><replaceable class="PARAMETER">expression</replaceable></term>
 119     <listitem>
 120      <para>
 121       An expression or value to assign to the corresponding <replaceable
 122       class="PARAMETER">column</replaceable>.
 123      </para>
 124     </listitem>
 125    </varlistentry>
 126
 127    <varlistentry>
 128     <term><literal>DEFAULT</literal></term>
 129     <listitem>
 130      <para>
 131       The corresponding <replaceable>column</replaceable> will be filled with
 132       its default value.
 133      </para>
 134     </listitem>
 135    </varlistentry>
 136
 137    <varlistentry>
 138     <term><replaceable class="PARAMETER">query</replaceable></term>
 139     <listitem>
 140      <para>
 141       A query (<command>SELECT</command> statement) that supplies the
 142       rows to be inserted.  Refer to the
 143       <xref linkend="sql-select" endterm="sql-select-title">
 144       statement for a description of the syntax.
 145      </para>
 146     </listitem>
 147    </varlistentry>
 148
 149    <varlistentry>
 150     <term><replaceable class="PARAMETER">output_expression</replaceable></term>
 151     <listitem>
 152      <para>
 153       An expression to be computed and returned by the <command>INSERT</>
 154       command after each row is inserted.  The expression can use any
 155       column names of the <replaceable class="PARAMETER">table</replaceable>.
 156       Write <literal>*</> to return all columns of the inserted row(s).
 157      </para>
 158     </listitem>
 159    </varlistentry>
 160
 161    <varlistentry>
 162     <term><replaceable class="PARAMETER">output_name</replaceable></term>
 163     <listitem>
 164      <para>
 165       A name to use for a returned column.
 166      </para>
 167     </listitem>
 168    </varlistentry>
 169   </variablelist>
 170  </refsect1>
 171
 172  <refsect1>
 173   <title>Outputs</title>
 174
 175   <para>
 176    On successful completion, an <command>INSERT</> command returns a command
 177    tag of the form
 178 <screen>
 179 INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
 180 </screen>
 181    The <replaceable class="parameter">count</replaceable> is the number
 182    of rows inserted.  If <replaceable class="parameter">count</replaceable>
 183    is exactly one, and the target table has OIDs, then
 184    <replaceable class="parameter">oid</replaceable> is the
 185    <acronym>OID</acronym> assigned to the inserted row.  Otherwise
 186    <replaceable class="parameter">oid</replaceable> is zero.
 187   </para>
 188
 189   <para>
 190    If the <command>INSERT</> command contains a <literal>RETURNING</>
 191    clause, the result will be similar to that of a <command>SELECT</>
 192    statement containing the columns and values defined in the
 193    <literal>RETURNING</> list, computed over the row(s) inserted by the
 194    command.
 195   </para>
 196  </refsect1>
 197
 198  <refsect1>
 199   <title>Examples</title>
 200
 201   <para>
 202    Insert a single row into table <literal>films</literal>:
 203
 204 <programlisting>
 205 INSERT INTO films VALUES
 206     ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
 207 </programlisting>
 208   </para>
 209
 210   <para>
 211    In this example, the <literal>len</literal> column is
 212    omitted and therefore it will have the default value:
 213
 214 <programlisting>
 215 INSERT INTO films (code, title, did, date_prod, kind)
 216     VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
 217 </programlisting>
 218   </para>
 219
 220   <para>
 221    This example uses the <literal>DEFAULT</literal> clause for
 222    the date columns rather than specifying a value:
 223
 224 <programlisting>
 225 INSERT INTO films VALUES
 226     ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
 227 INSERT INTO films (code, title, did, date_prod, kind)
 228     VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
 229 </programlisting>
 230   </para>
 231
 232   <para>
 233    To insert a row consisting entirely of default values:
 234
 235 <programlisting>
 236 INSERT INTO films DEFAULT VALUES;
 237 </programlisting>
 238   </para>
 239
 240   <para>
 241    To insert multiple rows using the multirow <command>VALUES</> syntax:
 242
 243 <programlisting>
 244 INSERT INTO films (code, title, did, date_prod, kind) VALUES
 245     ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
 246     ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
 247 </programlisting>
 248   </para>
 249
 250   <para>
 251    This example inserts some rows into table
 252    <literal>films</literal> from a table <literal>tmp_films</literal>
 253    with the same column layout as <literal>films</literal>:
 254
 255 <programlisting>
 256 INSERT INTO films SELECT * FROM tmp_films WHERE date_prod &lt; '2004-05-07';
 257 </programlisting>
 258   </para>
 259
 260   <para>
 261    This example inserts into array columns:
 262
 263 <programlisting>
 264 -- Create an empty 3x3 gameboard for noughts-and-crosses
 265 INSERT INTO tictactoe (game, board[1:3][1:3])
 266     VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
 267 -- The subscripts in the above example aren't really needed
 268 INSERT INTO tictactoe (game, board)
 269     VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
 270 </programlisting>
 271   </para>
 272
 273   <para>
 274    Insert a single row into table <literal>distributors</literal>, returning
 275    the sequence number generated by the <literal>DEFAULT</literal> clause:
 276
 277 <programlisting>
 278 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
 279    RETURNING did;
 280 </programlisting>
 281   </para>
 282  </refsect1>
 283
 284  <refsect1>
 285   <title>Compatibility</title>
 286
 287   <para>
 288    <command>INSERT</command> conforms to the SQL standard, except that
 289    the <literal>RETURNING</> clause is a
 290    <productname>PostgreSQL</productname> extension.  Also, the case in
 291    which a column name list is omitted, but not all the columns are
 292    filled from the <literal>VALUES</> clause or <replaceable>query</>,
 293    is disallowed by the standard.
 294   </para>
 295
 296   <para>
 297    Possible limitations of the <replaceable
 298    class="PARAMETER">query</replaceable> clause are documented under
 299    <xref linkend="sql-select" endterm="sql-select-title">.
 300   </para>
 301  </refsect1>
 302 </refentry>