2 $PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.32 2006/09/16 00:30:18 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-INSERT">
8 <refentrytitle id="SQL-INSERT-TITLE">INSERT</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
13 <refname>INSERT</refname>
14 <refpurpose>create new rows in a table</refpurpose>
17 <indexterm zone="sql-insert">
18 <primary>INSERT</primary>
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> ] [, ...] ]
30 <title>Description</title>
33 <command>INSERT</command> inserts new rows into a table.
34 One can insert rows specified by value expressions,
35 or rows computed as a result of a query.
39 The target column names may 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.
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.
55 If the expression for any column is not of the correct data type,
56 automatic type conversion will be attempted.
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</>.
70 You must have <literal>INSERT</literal> privilege to a table in
71 order to insert into it. If you use the <replaceable
72 class="PARAMETER">query</replaceable> clause to insert rows from a
73 query, you also need to have <literal>SELECT</literal> privilege on
74 any table used in the query.
79 <title>Parameters</title>
83 <term><replaceable class="PARAMETER">table</replaceable></term>
86 The name (optionally schema-qualified) of an existing table.
92 <term><replaceable class="PARAMETER">column</replaceable></term>
95 The name of a column in <replaceable class="PARAMETER">table</replaceable>.
96 The column name can be qualified with a subfield name or array
97 subscript, if needed. (Inserting into only some fields of a
98 composite column leaves the other fields null.)
104 <term><literal>DEFAULT VALUES</literal></term>
107 All columns will be filled with their default values.
113 <term><replaceable class="PARAMETER">expression</replaceable></term>
116 An expression or value to assign to the corresponding <replaceable
117 class="PARAMETER">column</replaceable>.
123 <term><literal>DEFAULT</literal></term>
126 The corresponding <replaceable>column</replaceable> will be filled with
133 <term><replaceable class="PARAMETER">query</replaceable></term>
136 A query (<command>SELECT</command> statement) that supplies the
137 rows to be inserted. Refer to the
138 <xref linkend="sql-select" endterm="sql-select-title">
139 statement for a description of the syntax.
145 <term><replaceable class="PARAMETER">output_expression</replaceable></term>
148 An expression to be computed and returned by the <command>INSERT</>
149 command after each row is inserted. The expression may use any
150 column names of the <replaceable class="PARAMETER">table</replaceable>.
151 Write <literal>*</> to return all columns of the inserted row(s).
157 <term><replaceable class="PARAMETER">output_name</replaceable></term>
160 A name to use for a returned column.
168 <title>Outputs</title>
171 On successful completion, an <command>INSERT</> command returns a command
174 INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
176 The <replaceable class="parameter">count</replaceable> is the number
177 of rows inserted. If <replaceable class="parameter">count</replaceable>
178 is exactly one, and the target table has OIDs, then
179 <replaceable class="parameter">oid</replaceable> is the
180 <acronym>OID</acronym> assigned to the inserted row. Otherwise
181 <replaceable class="parameter">oid</replaceable> is zero.
185 If the <command>INSERT</> command contains a <literal>RETURNING</>
186 clause, the result will be similar to that of a <command>SELECT</>
187 statement containing the columns and values defined in the
188 <literal>RETURNING</> list, computed over the row(s) inserted by the
194 <title>Examples</title>
197 Insert a single row into table <literal>films</literal>:
200 INSERT INTO films VALUES
201 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
206 In this example, the <literal>len</literal> column is
207 omitted and therefore it will have the default value:
210 INSERT INTO films (code, title, did, date_prod, kind)
211 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
216 This example uses the <literal>DEFAULT</literal> clause for
217 the date columns rather than specifying a value:
220 INSERT INTO films VALUES
221 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
222 INSERT INTO films (code, title, did, date_prod, kind)
223 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
228 To insert a row consisting entirely of default values:
231 INSERT INTO films DEFAULT VALUES;
236 This example inserts some rows into table
237 <literal>films</literal> from a table <literal>tmp_films</literal>
238 with the same column layout as <literal>films</literal>:
241 INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
246 This example inserts into array columns:
249 -- Create an empty 3x3 gameboard for noughts-and-crosses
250 INSERT INTO tictactoe (game, board[1:3][1:3])
251 VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
252 -- The subscripts in the above example aren't really needed
253 INSERT INTO tictactoe (game, board)
254 VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
259 Insert a single row into table <literal>distributors</literal>, returning
260 the sequence number generated by the <literal>DEFAULT</literal> clause:
263 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
270 <title>Compatibility</title>
273 <command>INSERT</command> conforms to the SQL standard, except that
274 the <literal>RETURNING</> clause is a
275 <productname>PostgreSQL</productname> extension. Also, the case in
276 which a column name list is omitted, but not all the columns are
277 filled from the <literal>VALUES</> clause or <replaceable>query</>,
278 is disallowed by the standard.
282 Possible limitations of the <replaceable
283 class="PARAMETER">query</replaceable> clause are documented under
284 <xref linkend="sql-select" endterm="sql-select-title">.