2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v 1.24 2003/08/31 17:32:23 petere 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> allows one to insert new rows into a
34 a single row at a time or several rows as a result of a query.
38 The columns in the target list may be listed in any order.
39 Each column not present in the target list will be inserted
40 using a default value, either its declared default value
45 If the expression for each column is not of the correct data type,
46 automatic type conversion will be attempted.
50 You must have <literal>INSERT</literal> privilege to a table in
51 order to insert into it. If you use the <replaceable
52 class="PARAMETER">query</replaceable> clause to insert rows from a
53 query, you also need to have <literal>SELECT</literal> privilege on
54 any table used in the query.
59 <title>Parameters</title>
63 <term><replaceable class="PARAMETER">table</replaceable></term>
66 The name (optionally schema-qualified) of an existing table.
72 <term><replaceable class="PARAMETER">column</replaceable></term>
75 The name of a column in <replaceable class="PARAMETER">table</replaceable>.
81 <term><literal>DEFAULT VALUES</literal></term>
84 All columns will be filled with their default values.
90 <term><replaceable class="PARAMETER">expression</replaceable></term>
93 An expression or value to assign to <replaceable
94 class="PARAMETER">column</replaceable>.
100 <term><literal>DEFAULT</literal></term>
103 This column will be filled with its default value.
109 <term><replaceable class="PARAMETER">query</replaceable></term>
112 A query (<command>SELECT</command> statement) that supplies the
113 rows to be inserted. Refer to the <command>SELECT</command>
114 statement for a description of the syntax.
122 <title>Diagnostics</title>
126 <term><computeroutput>INSERT <replaceable>oid</replaceable> 1</computeroutput></term>
129 Message returned if only one row was inserted.
130 <returnvalue><replaceable>oid</replaceable></returnvalue> is the
131 <acronym>OID</acronym> of the inserted row.
137 <term><computeroutput>INSERT 0 <replaceable>count</replaceable></computeroutput></term>
140 Message returned if more than one rows were inserted.
141 <replaceable>count</replaceable> is the number of rows inserted.
149 <title>Examples</title>
152 Insert a single row into table <literal>films</literal>:
155 INSERT INTO films VALUES
156 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
161 In this second example, the last column <literal>len</literal> is
162 omitted and therefore it will have the default value of null:
165 INSERT INTO films (code, title, did, date_prod, kind)
166 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
171 The third example uses the <literal>DEFAULT</literal> clause for
172 the date columns rather than specifying a value:
175 INSERT INTO films VALUES
176 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
177 INSERT INTO films (code, title, did, date_prod, kind)
178 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
183 This examples inserts several rows into table
184 <literal>films</literal> from table <literal>tmp</literal>:
187 INSERT INTO films SELECT * FROM tmp;
192 This example inserts into array columns:
195 -- Create an empty 3x3 gameboard for noughts-and-crosses
196 -- (all of these commands create the same board)
197 INSERT INTO tictactoe (game, board[1:3][1:3])
198 VALUES (1,'{{"","",""},{},{"",""}}');
199 INSERT INTO tictactoe (game, board[3][3])
201 INSERT INTO tictactoe (game, board)
202 VALUES (3,'{{,,},{,,},{,,}}');
208 <title>Compatibility</title>
211 <command>INSERT</command> conforms fully to the SQL standard.
212 Possible limitations of the <replaceable
213 class="PARAMETER">query</replaceable> clause are documented under
214 <xref linkend="sql-select" endterm="sql-select-title">.
219 <!-- Keep this comment at the end of the file
224 sgml-minimize-attributes:nil
225 sgml-always-quote-attributes:t
228 sgml-parent-document:nil
229 sgml-default-dtd-file:"../reference.ced"
230 sgml-exposed-tags:nil
231 sgml-local-catalogs:"/usr/lib/sgml/catalog"
232 sgml-local-ecat-files:nil