~ubuntu-branches/ubuntu/oneiric/postgresql-9.1/oneiric-security

{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }

[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>INSERT</command> inserts new rows into a table.

One can insert one or more rows specified by value expressions,

or zero or more rows resulting from a query.

</para>

<para>

The target column names can 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 <replaceable>N</> column

names, if there are only <replaceable>N</> columns supplied by the

<literal>VALUES</> clause or <replaceable>query</>. The values

supplied by the <literal>VALUES</> clause or <replaceable>query</> are

associated with the explicit or implicit column list left-to-right.

</para>

<para>

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.

</para>

<para>

If the expression for any column is not of the correct data type,

automatic type conversion will be attempted.

</para>

<para>

The optional <literal>RETURNING</> clause causes <command>INSERT</>

to compute and return value(s) based on each row actually inserted.

This is primarily useful for obtaining values that were supplied by

defaults, such as a serial sequence number. However, any expression

using the table's columns is allowed. The syntax of the

<literal>RETURNING</> list is identical to that of the output list

of <command>SELECT</>.

</para>

<para>

You must have <literal>INSERT</literal> privilege on a table in

order to insert into it. If a column list is specified, you only

need <literal>INSERT</literal> privilege on the listed columns.

Use of the <literal>RETURNING</> clause requires <literal>SELECT</>

privilege on all columns mentioned in <literal>RETURNING</>.

If you use the <replaceable

class="PARAMETER">query</replaceable> clause to insert rows from a

query, you of course need to have <literal>SELECT</literal> privilege on

any table or column used in the query.

</para>

</refsect1>

<title>Parameters</title>

<term><replaceable class="parameter">with_query</replaceable></term>

<para>

The <literal>WITH</literal> clause allows you to specify one or more

subqueries that can be referenced by name in the <command>INSERT</>

query. See <xref linkend="queries-with"> and <xref linkend="sql-select">

for details.

</para>

<para>

It is possible for the <replaceable class="parameter">query</replaceable>

(<command>SELECT</command> statement)

100

to also contain a <literal>WITH</literal> clause. In such a case both

101

sets of <replaceable>with_query</replaceable> can be referenced within

102

the <replaceable class="parameter">query</replaceable>, but the

103

second one takes precedence since it is more closely nested.

104

</para>

105

</listitem>

106

</varlistentry>

107

108

109

<term><replaceable class="PARAMETER">table</replaceable></term>

110

111

<para>

112

The name (optionally schema-qualified) of an existing table.

113

</para>

114

</listitem>

115

</varlistentry>

116

117

118

<term><replaceable class="PARAMETER">column</replaceable></term>

119

120

<para>

121

The name of a column in <replaceable class="PARAMETER">table</replaceable>.

122

The column name can be qualified with a subfield name or array

123

subscript, if needed. (Inserting into only some fields of a

124

composite column leaves the other fields null.)

125

</para>

126

</listitem>

127

</varlistentry>

128

129

130

<term><literal>DEFAULT VALUES</literal></term>

131

132

<para>

133

All columns will be filled with their default values.

134

</para>

135

</listitem>

136

</varlistentry>

137

138

139

<term><replaceable class="PARAMETER">expression</replaceable></term>

140

141

<para>

142

An expression or value to assign to the corresponding <replaceable

143

class="PARAMETER">column</replaceable>.

144

</para>

145

</listitem>

146

</varlistentry>

147

148

149

<term><literal>DEFAULT</literal></term>

150

151

<para>

152

The corresponding <replaceable>column</replaceable> will be filled with

153

its default value.

154

</para>

155

</listitem>

156

</varlistentry>

157

158

159

<term><replaceable class="PARAMETER">query</replaceable></term>

160

161

<para>

162

A query (<command>SELECT</command> statement) that supplies the

163

rows to be inserted. Refer to the

164

165

statement for a description of the syntax.

166

</para>

167

</listitem>

168

</varlistentry>

169

170

171

<term><replaceable class="PARAMETER">output_expression</replaceable></term>

172

173

<para>

174

An expression to be computed and returned by the <command>INSERT</>

175

command after each row is inserted. The expression can use any

176

column names of the <replaceable class="PARAMETER">table</replaceable>.

177

Write <literal>*</> to return all columns of the inserted row(s).

178

</para>

179

</listitem>

180

</varlistentry>

181

182

183

<term><replaceable class="PARAMETER">output_name</replaceable></term>

184

185

<para>

186

A name to use for a returned column.

187

</para>

188

</listitem>

189

</varlistentry>

190

</variablelist>

191

</refsect1>

192

193

194

<title>Outputs</title>

195

196

<para>

197

On successful completion, an <command>INSERT</> command returns a command

198

tag of the form

199

200

INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable>

201

</screen>

202

The <replaceable class="parameter">count</replaceable> is the number

203

of rows inserted. If <replaceable class="parameter">count</replaceable>

204

is exactly one, and the target table has OIDs, then

205

<replaceable class="parameter">oid</replaceable> is the

206

<acronym>OID</acronym> assigned to the inserted row. Otherwise

207

<replaceable class="parameter">oid</replaceable> is zero.

208

</para>

209

210

<para>

211

If the <command>INSERT</> command contains a <literal>RETURNING</>

212

clause, the result will be similar to that of a <command>SELECT</>

213

statement containing the columns and values defined in the

214

<literal>RETURNING</> list, computed over the row(s) inserted by the

215

command.

216

</para>

217

</refsect1>

218

219

220

<title>Examples</title>

221

222

<para>

223

Insert a single row into table <literal>films</literal>:

224

225

226

INSERT INTO films VALUES

227

('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

228

</programlisting>

229

</para>

230

231

<para>

232

In this example, the <literal>len</literal> column is

233

omitted and therefore it will have the default value:

234

235

236

INSERT INTO films (code, title, did, date_prod, kind)

237

VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

238

</programlisting>

239

</para>

240

241

<para>

242

This example uses the <literal>DEFAULT</literal> clause for

243

the date columns rather than specifying a value:

244

245

246

INSERT INTO films VALUES

247

('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');

248

INSERT INTO films (code, title, did, date_prod, kind)

249

VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

250

</programlisting>

251

</para>

252

253

<para>

254

To insert a row consisting entirely of default values:

255

256

257

INSERT INTO films DEFAULT VALUES;

258

</programlisting>

259

</para>

260

261

<para>

262

To insert multiple rows using the multirow <command>VALUES</> syntax:

263

264

265

INSERT INTO films (code, title, did, date_prod, kind) VALUES

266

('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),

267

('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

268

</programlisting>

269

</para>

270

271

<para>

272

This example inserts some rows into table

273

<literal>films</literal> from a table <literal>tmp_films</literal>

274

with the same column layout as <literal>films</literal>:

275

276

277

INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

278

</programlisting>

279

</para>

280

281

<para>

282

This example inserts into array columns:

283

284

285

-- Create an empty 3x3 gameboard for noughts-and-crosses

286

INSERT INTO tictactoe (game, board[1:3][1:3])

287

VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');

288

-- The subscripts in the above example aren't really needed

289

INSERT INTO tictactoe (game, board)

290

VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');

291

</programlisting>

292

</para>

293

294

<para>

295

Insert a single row into table <literal>distributors</literal>, returning

296

the sequence number generated by the <literal>DEFAULT</literal> clause:

297

298

299

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')

300

RETURNING did;

301

</programlisting>

302

</para>

303

304

<para>

305

Increment the sales count of the salesperson who manages the

306

account for Acme Corporation, and record the whole updated row

307

along with current time in a log table:

308

309

WITH upd AS (

310

UPDATE employees SET sales_count = sales_count + 1 WHERE id =

311

(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')

312

RETURNING *

313

)

314

INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

315

</programlisting>

316

</para>

317

</refsect1>

318

319

320

<title>Compatibility</title>

321

322

<para>

323

<command>INSERT</command> conforms to the SQL standard, except that

324

the <literal>RETURNING</> clause is a

325

<productname>PostgreSQL</productname> extension, as is the ability

326

to use <literal>WITH</> with <command>INSERT</>.

327

Also, the case in

328

which a column name list is omitted, but not all the columns are

329

filled from the <literal>VALUES</> clause or <replaceable>query</>,

330

is disallowed by the standard.

331

</para>

332

333

<para>

334

Possible limitations of the <replaceable

335

class="PARAMETER">query</replaceable> clause are documented under

336

<xref linkend="sql-select">.

337

</para>

338

</refsect1>

339

</refentry>

Older »