2 $PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.28 2005/01/04 00:39:53 tgl 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> }
29 <title>Description</title>
32 <command>INSERT</command> inserts new rows into a table.
33 One can insert a single row specified by value expressions,
34 or several rows as a result of a query.
38 The target column names may be listed in any order. If no list of
39 column names is given at all, the default is all the columns of the
40 table in their declared order; or the first <replaceable>N</> column
41 names, if there are only <replaceable>N</> columns supplied by the
42 <literal>VALUES</> clause or <replaceable>query</>. The values
43 supplied by the <literal>VALUES</> clause or <replaceable>query</> are
44 associated with the explicit or implicit column list left-to-right.
48 Each column not present in the explicit or implicit column list will be
49 filled with a default value, either its declared default value
50 or null if there is none.
54 If the expression for any column is not of the correct data type,
55 automatic type conversion will be attempted.
59 You must have <literal>INSERT</literal> privilege to a table in
60 order to insert into it. If you use the <replaceable
61 class="PARAMETER">query</replaceable> clause to insert rows from a
62 query, you also need to have <literal>SELECT</literal> privilege on
63 any table used in the query.
68 <title>Parameters</title>
72 <term><replaceable class="PARAMETER">table</replaceable></term>
75 The name (optionally schema-qualified) of an existing table.
81 <term><replaceable class="PARAMETER">column</replaceable></term>
84 The name of a column in <replaceable class="PARAMETER">table</replaceable>.
85 The column name can be qualified with a subfield name or array
86 subscript, if needed. (Inserting into only some fields of a
87 composite column leaves the other fields null.)
93 <term><literal>DEFAULT VALUES</literal></term>
96 All columns will be filled with their default values.
102 <term><replaceable class="PARAMETER">expression</replaceable></term>
105 An expression or value to assign to the corresponding <replaceable
106 class="PARAMETER">column</replaceable>.
112 <term><literal>DEFAULT</literal></term>
115 The corresponding <replaceable>column</replaceable> will be filled with
122 <term><replaceable class="PARAMETER">query</replaceable></term>
125 A query (<command>SELECT</command> statement) that supplies the
126 rows to be inserted. Refer to the <command>SELECT</command>
127 statement for a description of the syntax.
135 <title>Outputs</title>
138 On successful completion, an <command>INSERT</> command returns a command
141 INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
143 The <replaceable class="parameter">count</replaceable> is the number
144 of rows inserted. If <replaceable class="parameter">count</replaceable>
145 is exactly one, and the target table has OIDs, then
146 <replaceable class="parameter">oid</replaceable> is the
147 <acronym>OID</acronym> assigned to the inserted row. Otherwise
148 <replaceable class="parameter">oid</replaceable> is zero.
153 <title>Examples</title>
156 Insert a single row into table <literal>films</literal>:
159 INSERT INTO films VALUES
160 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
165 In this second example, the <literal>len</literal> column is
166 omitted and therefore it will have the default value:
169 INSERT INTO films (code, title, did, date_prod, kind)
170 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
175 The third example uses the <literal>DEFAULT</literal> clause for
176 the date columns rather than specifying a value:
179 INSERT INTO films VALUES
180 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
181 INSERT INTO films (code, title, did, date_prod, kind)
182 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
187 This example inserts several rows into table
188 <literal>films</literal> from table <literal>tmp</literal>:
191 INSERT INTO films SELECT * FROM tmp;
196 This example inserts into array columns:
199 -- Create an empty 3x3 gameboard for noughts-and-crosses
200 -- (these commands create the same board)
201 INSERT INTO tictactoe (game, board[1:3][1:3])
202 VALUES (1,'{{"","",""},{"","",""},{"","",""}}');
203 INSERT INTO tictactoe (game, board)
204 VALUES (2,'{{,,},{,,},{,,}}');
210 <title>Compatibility</title>
213 <command>INSERT</command> conforms to the SQL standard. The case in
214 which a column name list is omitted, but not all the columns are
215 filled from the <literal>VALUES</> clause or <replaceable>query</>,
216 is disallowed by the standard.
220 Possible limitations of the <replaceable
221 class="PARAMETER">query</replaceable> clause are documented under
222 <xref linkend="sql-select" endterm="sql-select-title">.
227 <!-- Keep this comment at the end of the file
232 sgml-minimize-attributes:nil
233 sgml-always-quote-attributes:t
236 sgml-parent-document:nil
237 sgml-default-dtd-file:"../reference.ced"
238 sgml-exposed-tags:nil
239 sgml-local-catalogs:"/usr/lib/sgml/catalog"
240 sgml-local-ecat-files:nil