2 $PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.37 2008/11/14 10:22:47 petere Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-INSERT">
8 <refentrytitle id="SQL-INSERT-TITLE">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 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> ] [, ...] ]
31 <title>Description</title>
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.
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.
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.
56 If the expression for any column is not of the correct data type,
57 automatic type conversion will be attempted.
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</>.
71 You must have <literal>INSERT</literal> privilege on a table in
72 order to insert into it, and <literal>SELECT</> privilege on it to
73 use <literal>RETURNING</>. If you use the <replaceable
74 class="PARAMETER">query</replaceable> clause to insert rows from a
75 query, you also need to have <literal>SELECT</literal> privilege on
76 any table used in the query.
81 <title>Parameters</title>
85 <term><replaceable class="PARAMETER">table</replaceable></term>
88 The name (optionally schema-qualified) of an existing table.
94 <term><replaceable class="PARAMETER">column</replaceable></term>
97 The name of a column in <replaceable class="PARAMETER">table</replaceable>.
98 The column name can be qualified with a subfield name or array
99 subscript, if needed. (Inserting into only some fields of a
100 composite column leaves the other fields null.)
106 <term><literal>DEFAULT VALUES</literal></term>
109 All columns will be filled with their default values.
115 <term><replaceable class="PARAMETER">expression</replaceable></term>
118 An expression or value to assign to the corresponding <replaceable
119 class="PARAMETER">column</replaceable>.
125 <term><literal>DEFAULT</literal></term>
128 The corresponding <replaceable>column</replaceable> will be filled with
135 <term><replaceable class="PARAMETER">query</replaceable></term>
138 A query (<command>SELECT</command> statement) that supplies the
139 rows to be inserted. Refer to the
140 <xref linkend="sql-select" endterm="sql-select-title">
141 statement for a description of the syntax.
147 <term><replaceable class="PARAMETER">output_expression</replaceable></term>
150 An expression to be computed and returned by the <command>INSERT</>
151 command after each row is inserted. The expression can use any
152 column names of the <replaceable class="PARAMETER">table</replaceable>.
153 Write <literal>*</> to return all columns of the inserted row(s).
159 <term><replaceable class="PARAMETER">output_name</replaceable></term>
162 A name to use for a returned column.
170 <title>Outputs</title>
173 On successful completion, an <command>INSERT</> command returns a command
176 INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
178 The <replaceable class="parameter">count</replaceable> is the number
179 of rows inserted. If <replaceable class="parameter">count</replaceable>
180 is exactly one, and the target table has OIDs, then
181 <replaceable class="parameter">oid</replaceable> is the
182 <acronym>OID</acronym> assigned to the inserted row. Otherwise
183 <replaceable class="parameter">oid</replaceable> is zero.
187 If the <command>INSERT</> command contains a <literal>RETURNING</>
188 clause, the result will be similar to that of a <command>SELECT</>
189 statement containing the columns and values defined in the
190 <literal>RETURNING</> list, computed over the row(s) inserted by the
196 <title>Examples</title>
199 Insert a single row into table <literal>films</literal>:
202 INSERT INTO films VALUES
203 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
208 In this example, the <literal>len</literal> column is
209 omitted and therefore it will have the default value:
212 INSERT INTO films (code, title, did, date_prod, kind)
213 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
218 This example uses the <literal>DEFAULT</literal> clause for
219 the date columns rather than specifying a value:
222 INSERT INTO films VALUES
223 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
224 INSERT INTO films (code, title, did, date_prod, kind)
225 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
230 To insert a row consisting entirely of default values:
233 INSERT INTO films DEFAULT VALUES;
238 To insert multiple rows using the multirow <command>VALUES</> syntax:
241 INSERT INTO films (code, title, did, date_prod, kind) VALUES
242 ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
243 ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
248 This example inserts some rows into table
249 <literal>films</literal> from a table <literal>tmp_films</literal>
250 with the same column layout as <literal>films</literal>:
253 INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
258 This example inserts into array columns:
261 -- Create an empty 3x3 gameboard for noughts-and-crosses
262 INSERT INTO tictactoe (game, board[1:3][1:3])
263 VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
264 -- The subscripts in the above example aren't really needed
265 INSERT INTO tictactoe (game, board)
266 VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
271 Insert a single row into table <literal>distributors</literal>, returning
272 the sequence number generated by the <literal>DEFAULT</literal> clause:
275 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
282 <title>Compatibility</title>
285 <command>INSERT</command> conforms to the SQL standard, except that
286 the <literal>RETURNING</> clause is a
287 <productname>PostgreSQL</productname> extension. Also, the case in
288 which a column name list is omitted, but not all the columns are
289 filled from the <literal>VALUES</> clause or <replaceable>query</>,
290 is disallowed by the standard.
294 Possible limitations of the <replaceable
295 class="PARAMETER">query</replaceable> clause are documented under
296 <xref linkend="sql-select" endterm="sql-select-title">.