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