2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v 1.20 2003/01/19 00:13:29 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-INSERT">
8 <refentrytitle id="SQL-INSERT-TITLE">INSERT</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
16 create new rows in a table
21 <date>2000-08-08</date>
24 INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
25 { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | SELECT <replaceable class="PARAMETER">query</replaceable> }
28 <refsect2 id="R2-SQL-INSERT-1">
37 <term><replaceable class="PARAMETER">table</replaceable></term>
40 The name (optionally schema-qualified) of an existing table.
46 <term><replaceable class="PARAMETER">column</replaceable></term>
49 The name of a column in <replaceable class="PARAMETER">table</replaceable>.
55 <term>DEFAULT VALUES</term>
58 All columns will be filled by null values or by values specified
59 when the table was created using <literal>DEFAULT</> clauses.
65 <term><replaceable class="PARAMETER">expression</replaceable></term>
68 A valid expression or value to assign to <replaceable
69 class="PARAMETER">column</replaceable>.
75 <term><replaceable class="PARAMETER">DEFAULT</replaceable></term>
78 This column will be filled in by the column DEFAULT clause, or NULL if
79 a default is not available.
85 <term><replaceable class="PARAMETER">query</replaceable></term>
88 A valid query. Refer to the SELECT statement for a further description
97 <refsect2 id="R2-SQL-INSERT-2">
105 <term><computeroutput>
106 INSERT <replaceable>oid</replaceable> 1
107 </computeroutput></term>
110 Message returned if only one row was inserted.
111 <returnvalue><replaceable>oid</replaceable></returnvalue>
112 is the numeric <acronym>OID</acronym> of the inserted row.
117 <term><computeroutput>
118 INSERT 0 <replaceable>#</replaceable>
119 </computeroutput></term>
122 Message returned if more than one rows were inserted.
123 <returnvalue><replaceable>#</replaceable></returnvalue>
124 is the number of rows inserted.
133 <refsect1 id="R1-SQL-INSERT-1">
139 <command>INSERT</command> allows one to insert new rows into a
140 table. One can insert
141 a single row at a time or several rows as a result of a query.
142 The columns in the target list may be listed in any order.
146 Each column not present in the target list will be inserted
147 using a default value, either a declared DEFAULT value
148 or NULL. <productname>PostgreSQL</productname> will reject the new
149 column if a NULL is inserted into a column declared NOT NULL.
153 If the expression for each column
154 is not of the correct data type, automatic type coercion will be
159 You must have insert privilege to a table in order to append
160 to it, as well as select privilege on any table specified
165 <refsect1 id="R1-SQL-INSERT-2">
170 Insert a single row into table <literal>films</literal>:
173 INSERT INTO films VALUES
174 ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');
179 In this second example the last column <literal>len</literal> is
180 omitted and therefore it will have the default value of NULL:
183 INSERT INTO films (code, title, did, date_prod, kind)
184 VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
189 In the third example, we use the DEFAULT values for the date columns
190 rather than specifying an entry.
193 INSERT INTO films VALUES
194 ('UA502','Bananas',105,DEFAULT,'Comedy',INTERVAL '82 minute');
195 INSERT INTO films (code, title, did, date_prod, kind)
196 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
201 Insert a single row into table distributors; note that
202 only column <literal>name</literal> is specified, so the omitted
203 column <literal>did</literal> will be assigned its default value:
206 INSERT INTO distributors (name) VALUES ('British Lion');
211 Insert several rows into table films from table <literal>tmp</literal>:
214 INSERT INTO films SELECT * FROM tmp;
219 Insert into arrays (refer to the &cite-user; for further
220 information about arrays):
223 -- Create an empty 3x3 gameboard for noughts-and-crosses
224 -- (all of these queries create the same board attribute)
225 INSERT INTO tictactoe (game, board[1:3][1:3])
226 VALUES (1,'{{"","",""},{},{"",""}}');
227 INSERT INTO tictactoe (game, board[3][3])
229 INSERT INTO tictactoe (game, board)
230 VALUES (3,'{{,,},{,,},{,,}}');
235 <refsect1 id="R1-SQL-INSERT-3">
240 <refsect2 id="R2-SQL-INSERT-4">
245 <command>INSERT</command> is fully compatible with <acronym>SQL92</acronym>.
246 Possible limitations in features of the
247 <replaceable class="PARAMETER">query</replaceable>
248 clause are documented for
249 <xref linkend="sql-select" endterm="sql-select-title">.
255 <!-- Keep this comment at the end of the file
260 sgml-minimize-attributes:nil
261 sgml-always-quote-attributes:t
264 sgml-parent-document:nil
265 sgml-default-dtd-file:"../reference.ced"
266 sgml-exposed-tags:nil
267 sgml-local-catalogs:"/usr/lib/sgml/catalog"
268 sgml-local-ecat-files:nil