2
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.29 2005-01-09 05:57:45 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>
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> inserts new rows into a table.
33
One can insert a single row specified by value expressions,
34
or several rows as a result of a query.
38
The target column names may be listed in any order. If no list of
39
column names is given at all, the default is all the columns of the
40
table in their declared order; or the first <replaceable>N</> column
41
names, if there are only <replaceable>N</> columns supplied by the
42
<literal>VALUES</> clause or <replaceable>query</>. The values
43
supplied by the <literal>VALUES</> clause or <replaceable>query</> are
44
associated with the explicit or implicit column list left-to-right.
48
Each column not present in the explicit or implicit column list will be
49
filled with a default value, either its declared default value
50
or null if there is none.
54
If the expression for any column is not of the correct data type,
55
automatic type conversion will be attempted.
59
You must have <literal>INSERT</literal> privilege to a table in
60
order to insert into it. If you use the <replaceable
61
class="PARAMETER">query</replaceable> clause to insert rows from a
62
query, you also need to have <literal>SELECT</literal> privilege on
63
any table used in the query.
68
<title>Parameters</title>
72
<term><replaceable class="PARAMETER">table</replaceable></term>
75
The name (optionally schema-qualified) of an existing table.
81
<term><replaceable class="PARAMETER">column</replaceable></term>
84
The name of a column in <replaceable class="PARAMETER">table</replaceable>.
85
The column name can be qualified with a subfield name or array
86
subscript, if needed. (Inserting into only some fields of a
87
composite column leaves the other fields null.)
93
<term><literal>DEFAULT VALUES</literal></term>
96
All columns will be filled with their default values.
102
<term><replaceable class="PARAMETER">expression</replaceable></term>
105
An expression or value to assign to the corresponding <replaceable
106
class="PARAMETER">column</replaceable>.
112
<term><literal>DEFAULT</literal></term>
115
The corresponding <replaceable>column</replaceable> will be filled with
122
<term><replaceable class="PARAMETER">query</replaceable></term>
125
A query (<command>SELECT</command> statement) that supplies the
126
rows to be inserted. Refer to the <command>SELECT</command>
127
statement for a description of the syntax.
135
<title>Outputs</title>
138
On successful completion, an <command>INSERT</> command returns a command
141
INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>
143
The <replaceable class="parameter">count</replaceable> is the number
144
of rows inserted. If <replaceable class="parameter">count</replaceable>
145
is exactly one, and the target table has OIDs, then
146
<replaceable class="parameter">oid</replaceable> is the
147
<acronym>OID</acronym> assigned to the inserted row. Otherwise
148
<replaceable class="parameter">oid</replaceable> is zero.
153
<title>Examples</title>
156
Insert a single row into table <literal>films</literal>:
159
INSERT INTO films VALUES
160
('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
165
In this example, the <literal>len</literal> column is
166
omitted and therefore it will have the default value:
169
INSERT INTO films (code, title, did, date_prod, kind)
170
VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
175
This example uses the <literal>DEFAULT</literal> clause for
176
the date columns rather than specifying a value:
179
INSERT INTO films VALUES
180
('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
181
INSERT INTO films (code, title, did, date_prod, kind)
182
VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
187
To insert a row consisting entirely of default values:
190
INSERT INTO films DEFAULT VALUES;
195
This example inserts some rows into table
196
<literal>films</literal> from a table <literal>tmp_films</literal>
197
with the same column layout as <literal>films</literal>:
200
INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
205
This example inserts into array columns:
208
-- Create an empty 3x3 gameboard for noughts-and-crosses
209
-- (these commands create the same board)
210
INSERT INTO tictactoe (game, board[1:3][1:3])
211
VALUES (1,'{{"","",""},{"","",""},{"","",""}}');
212
INSERT INTO tictactoe (game, board)
213
VALUES (2,'{{,,},{,,},{,,}}');
219
<title>Compatibility</title>
222
<command>INSERT</command> conforms to the SQL standard. The case in
223
which a column name list is omitted, but not all the columns are
224
filled from the <literal>VALUES</> clause or <replaceable>query</>,
225
is disallowed by the standard.
229
Possible limitations of the <replaceable
230
class="PARAMETER">query</replaceable> clause are documented under
231
<xref linkend="sql-select" 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