1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>INSERT</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-grant.html" title="GRANT">
<link rel="next" href="sql-listen.html" title="LISTEN">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-insert"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>INSERT — create new rows in a table</p>
</div>
<a name="id777091"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">INSERT INTO <em class="replaceable"><code>table</code></em> [ ( <em class="replaceable"><code>column</code></em> [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) | <em class="replaceable"><code>query</code></em> }</pre>
</div>
<div class="refsect1" lang="en">
<a name="id777135"></a><h2>Description</h2>
<p> <code class="command">INSERT</code> inserts new rows into a table.
One can insert a single row specified by value expressions,
or several rows as a result of a query.
</p>
<p> The target column names may be listed in any order. If no list of
column names is given at all, the default is all the columns of the
table in their declared order; or the first <em class="replaceable"><code>N</code></em> column
names, if there are only <em class="replaceable"><code>N</code></em> columns supplied by the
<code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em>. The values
supplied by the <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em> are
associated with the explicit or implicit column list left-to-right.
</p>
<p> Each column not present in the explicit or implicit column list will be
filled with a default value, either its declared default value
or null if there is none.
</p>
<p> If the expression for any column is not of the correct data type,
automatic type conversion will be attempted.
</p>
<p> You must have <code class="literal">INSERT</code> privilege to a table in
order to insert into it. If you use the <em class="replaceable"><code>query</code></em> clause to insert rows from a
query, you also need to have <code class="literal">SELECT</code> privilege on
any table used in the query.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id777223"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<dt><span class="term"><em class="replaceable"><code>table</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of an existing table.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>column</code></em></span></dt>
<dd><p> The name of a column in <em class="replaceable"><code>table</code></em>.
The column name can be qualified with a subfield name or array
subscript, if needed. (Inserting into only some fields of a
composite column leaves the other fields null.)
</p></dd>
<dt><span class="term"><code class="literal">DEFAULT VALUES</code></span></dt>
<dd><p> All columns will be filled with their default values.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>expression</code></em></span></dt>
<dd><p> An expression or value to assign to the corresponding <em class="replaceable"><code>column</code></em>.
</p></dd>
<dt><span class="term"><code class="literal">DEFAULT</code></span></dt>
<dd><p> The corresponding <em class="replaceable"><code>column</code></em> will be filled with
its default value.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>query</code></em></span></dt>
<dd><p> A query (<code class="command">SELECT</code> statement) that supplies the
rows to be inserted. Refer to the <code class="command">SELECT</code>
statement for a description of the syntax.
</p></dd>
</dl></div>
</div>
<div class="refsect1" lang="en">
<a name="id777338"></a><h2>Outputs</h2>
<p> On successful completion, an <code class="command">INSERT</code> command returns a command
tag of the form
</p>
<pre class="screen">INSERT <em class="replaceable"><code>oid</code></em> <em class="replaceable"><code>count</code></em></pre>
<p>
The <em class="replaceable"><code>count</code></em> is the number
of rows inserted. If <em class="replaceable"><code>count</code></em>
is exactly one, and the target table has OIDs, then
<em class="replaceable"><code>oid</code></em> is the
<acronym class="acronym">OID</acronym> assigned to the inserted row. Otherwise
<em class="replaceable"><code>oid</code></em> is zero.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id777400"></a><h2>Examples</h2>
<p> Insert a single row into table <code class="literal">films</code>:
</p>
<pre class="programlisting">INSERT INTO films VALUES
('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');</pre>
<p>
</p>
<p> In this example, the <code class="literal">len</code> column is
omitted and therefore it will have the default value:
</p>
<pre class="programlisting">INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');</pre>
<p>
</p>
<p> This example uses the <code class="literal">DEFAULT</code> clause for
the date columns rather than specifying a value:
</p>
<pre class="programlisting">INSERT INTO films VALUES
('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
INSERT INTO films (code, title, did, date_prod, kind)
VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');</pre>
<p>
</p>
<p> To insert a row consisting entirely of default values:
</p>
<pre class="programlisting">INSERT INTO films DEFAULT VALUES;</pre>
<p>
</p>
<p> This example inserts some rows into table
<code class="literal">films</code> from a table <code class="literal">tmp_films</code>
with the same column layout as <code class="literal">films</code>:
</p>
<pre class="programlisting">INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';</pre>
<p>
</p>
<p> This example inserts into array columns:
</p>
<pre class="programlisting">-- Create an empty 3x3 gameboard for noughts-and-crosses
-- (these commands create the same board)
INSERT INTO tictactoe (game, board[1:3][1:3])
VALUES (1,'{{"","",""},{"","",""},{"","",""}}');
INSERT INTO tictactoe (game, board)
VALUES (2,'{{,,},{,,},{,,}}');</pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id777517"></a><h2>Compatibility</h2>
<p> <code class="command">INSERT</code> conforms to the SQL standard. The case in
which a column name list is omitted, but not all the columns are
filled from the <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em>,
is disallowed by the standard.
</p>
<p> Possible limitations of the <em class="replaceable"><code>query</code></em> clause are documented under
<a href="sql-select.html">SELECT</a>.
</p>
</div>
</div></body>
</html>
|
