2 doc/src/sgml/ref/insert.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-INSERT">
8 <refentrytitle>INSERT</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>INSERT</refname>
15 <refpurpose>create new rows in a table</refpurpose>
18 <indexterm zone="sql-insert">
19 <primary>INSERT</primary>
24 [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
25 INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
26 { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
27 [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
32 <title>Description</title>
35 <command>INSERT</command> inserts new rows into a table.
36 One can insert one or more rows specified by value expressions,
37 or zero or more rows resulting from a query.
41 The target column names can be listed in any order. If no list of
42 column names is given at all, the default is all the columns of the
43 table in their declared order; or the first <replaceable>N</> column
44 names, if there are only <replaceable>N</> columns supplied by the
45 <literal>VALUES</> clause or <replaceable>query</>. The values
46 supplied by the <literal>VALUES</> clause or <replaceable>query</> are
47 associated with the explicit or implicit column list left-to-right.
51 Each column not present in the explicit or implicit column list will be
52 filled with a default value, either its declared default value
53 or null if there is none.
57 If the expression for any column is not of the correct data type,
58 automatic type conversion will be attempted.
62 The optional <literal>RETURNING</> clause causes <command>INSERT</>
63 to compute and return value(s) based on each row actually inserted.
64 This is primarily useful for obtaining values that were supplied by
65 defaults, such as a serial sequence number. However, any expression
66 using the table's columns is allowed. The syntax of the
67 <literal>RETURNING</> list is identical to that of the output list
68 of <command>SELECT</>.
72 You must have <literal>INSERT</literal> privilege on a table in
73 order to insert into it. If a column list is specified, you only
74 need <literal>INSERT</literal> privilege on the listed columns.
75 Use of the <literal>RETURNING</> clause requires <literal>SELECT</>
76 privilege on all columns mentioned in <literal>RETURNING</>.
77 If you use the <replaceable
78 class="PARAMETER">query</replaceable> clause to insert rows from a
79 query, you of course need to have <literal>SELECT</literal> privilege on
80 any table or column used in the query.
85 <title>Parameters</title>
89 <term><replaceable class="parameter">with_query</replaceable></term>
92 The <literal>WITH</literal> clause allows you to specify one or more
93 subqueries that can be referenced by name in the <command>INSERT</>
94 query. See <xref linkend="queries-with"> and <xref linkend="sql-select">
98 It is possible for the <replaceable class="parameter">query</replaceable>
99 (<command>SELECT</command> statement)
100 to also contain a <literal>WITH</literal> clause. In such a case both
101 sets of <replaceable>with_query</replaceable> can be referenced within
102 the <replaceable class="parameter">query</replaceable>, but the
103 second one takes precedence since it is more closely nested.
109 <term><replaceable class="PARAMETER">table_name</replaceable></term>
112 The name (optionally schema-qualified) of an existing table.
118 <term><replaceable class="PARAMETER">column_name</replaceable></term>
121 The name of a column in the table named by <replaceable class="PARAMETER">table_name</replaceable>.
122 The column name can be qualified with a subfield name or array
123 subscript, if needed. (Inserting into only some fields of a
124 composite column leaves the other fields null.)
130 <term><literal>DEFAULT VALUES</literal></term>
133 All columns will be filled with their default values.
139 <term><replaceable class="PARAMETER">expression</replaceable></term>
142 An expression or value to assign to the corresponding column.
148 <term><literal>DEFAULT</literal></term>
151 The corresponding column will be filled with
158 <term><replaceable class="PARAMETER">query</replaceable></term>
161 A query (<command>SELECT</command> statement) that supplies the
162 rows to be inserted. Refer to the
163 <xref linkend="sql-select">
164 statement for a description of the syntax.
170 <term><replaceable class="PARAMETER">output_expression</replaceable></term>
173 An expression to be computed and returned by the <command>INSERT</>
174 command after each row is inserted. The expression can use any
175 column names of the table named by <replaceable class="PARAMETER">table_name</replaceable>.
176 Write <literal>*</> to return all columns of the inserted row(s).
182 <term><replaceable class="PARAMETER">output_name</replaceable></term>
185 A name to use for a returned column.
193 <title>Outputs</title>
196 On successful completion, an <command>INSERT</> command returns a command
199 INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
201 The <replaceable class="parameter">count</replaceable> is the number
202 of rows inserted. If <replaceable class="parameter">count</replaceable>
203 is exactly one, and the target table has OIDs, then
204 <replaceable class="parameter">oid</replaceable> is the
205 <acronym>OID</acronym> assigned to the inserted row. Otherwise
206 <replaceable class="parameter">oid</replaceable> is zero.
210 If the <command>INSERT</> command contains a <literal>RETURNING</>
211 clause, the result will be similar to that of a <command>SELECT</>
212 statement containing the columns and values defined in the
213 <literal>RETURNING</> list, computed over the row(s) inserted by the
219 <title>Examples</title>
222 Insert a single row into table <literal>films</literal>:
225 INSERT INTO films VALUES
226 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
231 In this example, the <literal>len</literal> column is
232 omitted and therefore it will have the default value:
235 INSERT INTO films (code, title, did, date_prod, kind)
236 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
241 This example uses the <literal>DEFAULT</literal> clause for
242 the date columns rather than specifying a value:
245 INSERT INTO films VALUES
246 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
247 INSERT INTO films (code, title, did, date_prod, kind)
248 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
253 To insert a row consisting entirely of default values:
256 INSERT INTO films DEFAULT VALUES;
261 To insert multiple rows using the multirow <command>VALUES</> syntax:
264 INSERT INTO films (code, title, did, date_prod, kind) VALUES
265 ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
266 ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
271 This example inserts some rows into table
272 <literal>films</literal> from a table <literal>tmp_films</literal>
273 with the same column layout as <literal>films</literal>:
276 INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
281 This example inserts into array columns:
284 -- Create an empty 3x3 gameboard for noughts-and-crosses
285 INSERT INTO tictactoe (game, board[1:3][1:3])
286 VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
287 -- The subscripts in the above example aren't really needed
288 INSERT INTO tictactoe (game, board)
289 VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
294 Insert a single row into table <literal>distributors</literal>, returning
295 the sequence number generated by the <literal>DEFAULT</literal> clause:
298 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
304 Increment the sales count of the salesperson who manages the
305 account for Acme Corporation, and record the whole updated row
306 along with current time in a log table:
309 UPDATE employees SET sales_count = sales_count + 1 WHERE id =
310 (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
313 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
314 </programlisting></para>
318 <title>Compatibility</title>
321 <command>INSERT</command> conforms to the SQL standard, except that
322 the <literal>RETURNING</> clause is a
323 <productname>PostgreSQL</productname> extension, as is the ability
324 to use <literal>WITH</> with <command>INSERT</>.
326 which a column name list is omitted, but not all the columns are
327 filled from the <literal>VALUES</> clause or <replaceable>query</>,
328 is disallowed by the standard.
332 Possible limitations of the <replaceable
333 class="PARAMETER">query</replaceable> clause are documented under
334 <xref linkend="sql-select">.