2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v 1.23 2003/07/03 16:32:03 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>
19 INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
20 { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | <replaceable class="PARAMETER">query</replaceable> }
25 <title>Description</title>
28 <command>INSERT</command> allows one to insert new rows into a
30 a single row at a time or several rows as a result of a query.
34 The columns in the target list may be listed in any order.
35 Each column not present in the target list will be inserted
36 using a default value, either its declared default value
41 If the expression for each column is not of the correct data type,
42 automatic type conversion will be attempted.
46 You must have <literal>INSERT</literal> privilege to a table in
47 order to insert into it. If you use the <replaceable
48 class="PARAMETER">query</replaceable> clause to insert rows from a
49 query, you also need to have <literal>SELECT</literal> privilege on
50 any table used in the query.
55 <title>Parameters</title>
59 <term><replaceable class="PARAMETER">table</replaceable></term>
62 The name (optionally schema-qualified) of an existing table.
68 <term><replaceable class="PARAMETER">column</replaceable></term>
71 The name of a column in <replaceable class="PARAMETER">table</replaceable>.
77 <term><literal>DEFAULT VALUES</literal></term>
80 All columns will be filled with their default values.
86 <term><replaceable class="PARAMETER">expression</replaceable></term>
89 An expression or value to assign to <replaceable
90 class="PARAMETER">column</replaceable>.
96 <term><literal>DEFAULT</literal></term>
99 This column will be filled with its default value.
105 <term><replaceable class="PARAMETER">query</replaceable></term>
108 A query (<command>SELECT</command> statement) that supplies the
109 rows to be inserted. Refer to the <command>SELECT</command>
110 statement for a description of the syntax.
118 <title>Diagnostics</title>
122 <term><computeroutput>INSERT <replaceable>oid</replaceable> 1</computeroutput></term>
125 Message returned if only one row was inserted.
126 <returnvalue><replaceable>oid</replaceable></returnvalue> is the
127 <acronym>OID</acronym> of the inserted row.
133 <term><computeroutput>INSERT 0 <replaceable>count</replaceable></computeroutput></term>
136 Message returned if more than one rows were inserted.
137 <replaceable>count</replaceable> is the number of rows inserted.
145 <title>Examples</title>
148 Insert a single row into table <literal>films</literal>:
151 INSERT INTO films VALUES
152 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
157 In this second example, the last column <literal>len</literal> is
158 omitted and therefore it will have the default value of null:
161 INSERT INTO films (code, title, did, date_prod, kind)
162 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
167 The third example uses the <literal>DEFAULT</literal> clause for
168 the date columns rather than specifying a value:
171 INSERT INTO films VALUES
172 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
173 INSERT INTO films (code, title, did, date_prod, kind)
174 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
179 This examples inserts several rows into table
180 <literal>films</literal> from table <literal>tmp</literal>:
183 INSERT INTO films SELECT * FROM tmp;
188 This example inserts into array columns:
191 -- Create an empty 3x3 gameboard for noughts-and-crosses
192 -- (all of these commands create the same board)
193 INSERT INTO tictactoe (game, board[1:3][1:3])
194 VALUES (1,'{{"","",""},{},{"",""}}');
195 INSERT INTO tictactoe (game, board[3][3])
197 INSERT INTO tictactoe (game, board)
198 VALUES (3,'{{,,},{,,},{,,}}');
204 <title>Compatibility</title>
207 <command>INSERT</command> conforms fully to the SQL standard.
208 Possible limitations of the <replaceable
209 class="PARAMETER">query</replaceable> clause are documented under
210 <xref linkend="sql-select" endterm="sql-select-title">.
215 <!-- Keep this comment at the end of the file
220 sgml-minimize-attributes:nil
221 sgml-always-quote-attributes:t
224 sgml-parent-document:nil
225 sgml-default-dtd-file:"../reference.ced"
226 sgml-exposed-tags:nil
227 sgml-local-catalogs:"/usr/lib/sgml/catalog"
228 sgml-local-ecat-files:nil