2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/insert.sgml,v 1.7 2000/03/27 17:14:43 thomas Exp $
6 <refentry id="SQL-INSERT">
8 <refentrytitle id="SQL-INSERT-TITLE">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 Inserts new rows into a table
23 <date>1999-07-20</date>
26 INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ]
27 { VALUES ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) | SELECT <replaceable class="PARAMETER">query</replaceable> }
30 <refsect2 id="R2-SQL-INSERT-1">
32 <date>1998-09-23</date>
41 <term><replaceable class="PARAMETER">table</replaceable></term>
44 The name of an existing table.
49 <term><replaceable class="PARAMETER">column</replaceable></term>
52 The name of a column in <replaceable class="PARAMETER">table</replaceable>.
58 <term><replaceable class="PARAMETER">expression</replaceable></term>
61 A valid expression or value to assign to <replaceable
62 class="PARAMETER">column</replaceable>.
68 <term><replaceable class="PARAMETER">query</replaceable></term>
71 A valid query. Refer to the SELECT statement for a further description
80 <refsect2 id="R2-SQL-INSERT-2">
82 <date>1998-09-23</date>
91 <term><computeroutput>
92 INSERT <replaceable>oid</replaceable> 1
93 </computeroutput></term>
96 Message returned if only one row was inserted.
97 <returnvalue><replaceable>oid</replaceable></returnvalue>
98 is the numeric <acronym>OID</acronym> of the inserted row.
103 <term><computeroutput>
104 INSERT 0 <replaceable>#</replaceable>
105 </computeroutput></term>
108 Message returned if more than one rows were inserted.
109 <returnvalue><replaceable>#</replaceable></returnvalue>
110 is the number of rows inserted.
119 <refsect1 id="R1-SQL-INSERT-1">
121 <date>1998-09-02</date>
128 <command>INSERT</command> allows one to insert new rows into a
129 class or table. One can insert
130 a single row at time or several rows as a result of a query.
131 The columns in the target list may be listed in any order.
135 Each column not present in the target list will be inserted
136 using a default value, either a declared DEFAULT value
137 or NULL. <productname>Postgres</productname> will reject the new
138 column if a NULL is inserted into a column declared NOT NULL.
142 If the expression for each column
143 is not of the correct data type, automatic type coercion will be
148 You must have insert privilege to a table in order to append
149 to it, as well as select privilege on any table specified
154 <refsect1 id="R1-SQL-INSERT-2">
159 Insert a single row into table <literal>films</literal>:
162 INSERT INTO films VALUES
163 ('UA502','Bananas',105,'1971-07-13','Comedy',INTERVAL '82 minute');
168 In this second example the column <literal>date_prod</literal> is
169 omitted and therefore it will have the default value of NULL:
172 INSERT INTO films (code, title, did, date_prod, kind)
173 VALUES ('T_601', 'Yojimbo', 106, DATE '1961-06-16', 'Drama');
178 Insert a single row into table distributors; note that
179 only column <literal>name</literal> is specified, so the omitted
180 column <literal>did</literal> will be assigned its default value:
183 INSERT INTO distributors (name) VALUES ('British Lion');
188 Insert several rows into table films from table <literal>tmp</literal>:
191 INSERT INTO films SELECT * FROM tmp;
196 Insert into arrays (refer to the
197 <citetitle>PostgreSQL User's Guide</citetitle> for further
198 information about arrays):
201 -- Create an empty 3x3 gameboard for noughts-and-crosses
202 -- (all of these queries create the same board attribute)
203 INSERT INTO tictactoe (game, board[1:3][1:3])
204 VALUES (1,'{{"","",""},{},{"",""}}');
205 INSERT INTO tictactoe (game, board[3][3])
207 INSERT INTO tictactoe (game, board)
208 VALUES (3,'{{,,},{,,},{,,}}');
213 <refsect1 id="R1-SQL-INSERT-3">
218 <refsect2 id="R2-SQL-INSERT-4">
220 <date>1998-09-23</date>
226 <command>INSERT</command> is fully compatible with <acronym>SQL92</acronym>.
227 Possible limitations in features of the
228 <replaceable class="PARAMETER">query</replaceable>
229 clause are documented for
230 <xref linkend="sql-select-title" endterm="sql-select-title">.
236 <!-- Keep this comment at the end of the file
241 sgml-minimize-attributes:nil
242 sgml-always-quote-attributes:t
245 sgml-parent-document:nil
246 sgml-default-dtd-file:"../reference.ced"
247 sgml-exposed-tags:nil
248 sgml-local-catalogs:"/usr/lib/sgml/catalog"
249 sgml-local-ecat-files:nil