2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v 1.17 2002/04/23 02:07:16 tgl 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> [, ...] ) | 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 NULLs or by values specified
59 when the table was created using 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">query</replaceable></term>
78 A valid query. Refer to the SELECT statement for a further description
87 <refsect2 id="R2-SQL-INSERT-2">
95 <term><computeroutput>
96 INSERT <replaceable>oid</replaceable> 1
97 </computeroutput></term>
100 Message returned if only one row was inserted.
101 <returnvalue><replaceable>oid</replaceable></returnvalue>
102 is the numeric <acronym>OID</acronym> of the inserted row.
107 <term><computeroutput>
108 INSERT 0 <replaceable>#</replaceable>
109 </computeroutput></term>
112 Message returned if more than one rows were inserted.
113 <returnvalue><replaceable>#</replaceable></returnvalue>
114 is the number of rows inserted.
123 <refsect1 id="R1-SQL-INSERT-1">
129 <command>INSERT</command> allows one to insert new rows into a
130 table. One can insert
131 a single row at a time or several rows as a result of a query.
132 The columns in the target list may be listed in any order.
136 Each column not present in the target list will be inserted
137 using a default value, either a declared DEFAULT value
138 or NULL. <productname>PostgreSQL</productname> will reject the new
139 column if a NULL is inserted into a column declared NOT NULL.
143 If the expression for each column
144 is not of the correct data type, automatic type coercion will be
149 You must have insert privilege to a table in order to append
150 to it, as well as select privilege on any table specified
155 <refsect1 id="R1-SQL-INSERT-2">
160 Insert a single row into table <literal>films</literal>:
163 INSERT INTO films VALUES
164 ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');
169 In this second example the last column <literal>len</literal> is
170 omitted and therefore it will have the default value of NULL:
173 INSERT INTO films (code, title, did, date_prod, kind)
174 VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
179 Insert a single row into table distributors; note that
180 only column <literal>name</literal> is specified, so the omitted
181 column <literal>did</literal> will be assigned its default value:
184 INSERT INTO distributors (name) VALUES ('British Lion');
189 Insert several rows into table films from table <literal>tmp</literal>:
192 INSERT INTO films SELECT * FROM tmp;
197 Insert into arrays (refer to the
198 <citetitle>PostgreSQL User's Guide</citetitle> for further
199 information about arrays):
202 -- Create an empty 3x3 gameboard for noughts-and-crosses
203 -- (all of these queries create the same board attribute)
204 INSERT INTO tictactoe (game, board[1:3][1:3])
205 VALUES (1,'{{"","",""},{},{"",""}}');
206 INSERT INTO tictactoe (game, board[3][3])
208 INSERT INTO tictactoe (game, board)
209 VALUES (3,'{{,,},{,,},{,,}}');
214 <refsect1 id="R1-SQL-INSERT-3">
219 <refsect2 id="R2-SQL-INSERT-4">
224 <command>INSERT</command> is fully compatible with <acronym>SQL92</acronym>.
225 Possible limitations in features of the
226 <replaceable class="PARAMETER">query</replaceable>
227 clause are documented for
228 <xref linkend="sql-select" endterm="sql-select-title">.
234 <!-- Keep this comment at the end of the file
239 sgml-minimize-attributes:nil
240 sgml-always-quote-attributes:t
243 sgml-parent-document:nil
244 sgml-default-dtd-file:"../reference.ced"
245 sgml-exposed-tags:nil
246 sgml-local-catalogs:"/usr/lib/sgml/catalog"
247 sgml-local-ecat-files:nil