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</replaceable> [ ( <replaceable class="PARAMETER">column</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</replaceable></term>
112 The name (optionally schema-qualified) of an existing table.
118 <term><replaceable class="PARAMETER">column</replaceable></term>
121 The name of a column in <replaceable class="PARAMETER">table</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 <replaceable
143 class="PARAMETER">column</replaceable>.
149 <term><literal>DEFAULT</literal></term>
152 The corresponding <replaceable>column</replaceable> will be filled with
159 <term><replaceable class="PARAMETER">query</replaceable></term>
162 A query (<command>SELECT</command> statement) that supplies the
163 rows to be inserted. Refer to the
164 <xref linkend="sql-select">
165 statement for a description of the syntax.
171 <term><replaceable class="PARAMETER">output_expression</replaceable></term>
174 An expression to be computed and returned by the <command>INSERT</>
175 command after each row is inserted. The expression can use any
176 column names of the <replaceable class="PARAMETER">table</replaceable>.
177 Write <literal>*</> to return all columns of the inserted row(s).
183 <term><replaceable class="PARAMETER">output_name</replaceable></term>
186 A name to use for a returned column.
194 <title>Outputs</title>
197 On successful completion, an <command>INSERT</> command returns a command
200 INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
202 The <replaceable class="parameter">count</replaceable> is the number
203 of rows inserted. If <replaceable class="parameter">count</replaceable>
204 is exactly one, and the target table has OIDs, then
205 <replaceable class="parameter">oid</replaceable> is the
206 <acronym>OID</acronym> assigned to the inserted row. Otherwise
207 <replaceable class="parameter">oid</replaceable> is zero.
211 If the <command>INSERT</> command contains a <literal>RETURNING</>
212 clause, the result will be similar to that of a <command>SELECT</>
213 statement containing the columns and values defined in the
214 <literal>RETURNING</> list, computed over the row(s) inserted by the
220 <title>Examples</title>
223 Insert a single row into table <literal>films</literal>:
226 INSERT INTO films VALUES
227 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
232 In this example, the <literal>len</literal> column is
233 omitted and therefore it will have the default value:
236 INSERT INTO films (code, title, did, date_prod, kind)
237 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
242 This example uses the <literal>DEFAULT</literal> clause for
243 the date columns rather than specifying a value:
246 INSERT INTO films VALUES
247 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
248 INSERT INTO films (code, title, did, date_prod, kind)
249 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
254 To insert a row consisting entirely of default values:
257 INSERT INTO films DEFAULT VALUES;
262 To insert multiple rows using the multirow <command>VALUES</> syntax:
265 INSERT INTO films (code, title, did, date_prod, kind) VALUES
266 ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
267 ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
272 This example inserts some rows into table
273 <literal>films</literal> from a table <literal>tmp_films</literal>
274 with the same column layout as <literal>films</literal>:
277 INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
282 This example inserts into array columns:
285 -- Create an empty 3x3 gameboard for noughts-and-crosses
286 INSERT INTO tictactoe (game, board[1:3][1:3])
287 VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
288 -- The subscripts in the above example aren't really needed
289 INSERT INTO tictactoe (game, board)
290 VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
295 Insert a single row into table <literal>distributors</literal>, returning
296 the sequence number generated by the <literal>DEFAULT</literal> clause:
299 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
305 Increment the sales count of the salesperson who manages the
306 account for Acme Corporation, and record the whole updated row
307 along with current time in a log table:
310 UPDATE employees SET sales_count = sales_count + 1 WHERE id =
311 (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
314 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
315 </programlisting></para>
319 <title>Compatibility</title>
322 <command>INSERT</command> conforms to the SQL standard, except that
323 the <literal>RETURNING</> clause is a
324 <productname>PostgreSQL</productname> extension, as is the ability
325 to use <literal>WITH</> with <command>INSERT</>.
327 which a column name list is omitted, but not all the columns are
328 filled from the <literal>VALUES</> clause or <replaceable>query</>,
329 is disallowed by the standard.
333 Possible limitations of the <replaceable
334 class="PARAMETER">query</replaceable> clause are documented under
335 <xref linkend="sql-select">.