codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>INSERT</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA" /><link rel="next" href="sql-listen.html" title="LISTEN" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">INSERT</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-listen.html" title="LISTEN">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-INSERT"><div class="titlepage"></div><a id="id-1.9.3.152.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">INSERT</span></h2><p>INSERT — create new rows in a table</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3[ WITH [ RECURSIVE ] <em class="replaceable"><code>with_query</code></em> [, ...] ]4INSERT INTO <em class="replaceable"><code>table_name</code></em> [ AS <em class="replaceable"><code>alias</code></em> ] [ ( <em class="replaceable"><code>column_name</code></em> [, ...] ) ]5 [ OVERRIDING { SYSTEM | USER } VALUE ]6 { DEFAULT VALUES | VALUES ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) [, ...] | <em class="replaceable"><code>query</code></em> }7 [ ON CONFLICT [ <em class="replaceable"><code>conflict_target</code></em> ] <em class="replaceable"><code>conflict_action</code></em> ]8 [ RETURNING * | <em class="replaceable"><code>output_expression</code></em> [ [ AS ] <em class="replaceable"><code>output_name</code></em> ] [, ...] ]9 10<span class="phrase">where <em class="replaceable"><code>conflict_target</code></em> can be one of:</span>11 12 ( { <em class="replaceable"><code>index_column_name</code></em> | ( <em class="replaceable"><code>index_expression</code></em> ) } [ COLLATE <em class="replaceable"><code>collation</code></em> ] [ <em class="replaceable"><code>opclass</code></em> ] [, ...] ) [ WHERE <em class="replaceable"><code>index_predicate</code></em> ]13 ON CONSTRAINT <em class="replaceable"><code>constraint_name</code></em>14 15<span class="phrase">and <em class="replaceable"><code>conflict_action</code></em> is one of:</span>16 17 DO NOTHING18 DO UPDATE SET { <em class="replaceable"><code>column_name</code></em> = { <em class="replaceable"><code>expression</code></em> | DEFAULT } |19 ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = [ ROW ] ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) |20 ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = ( <em class="replaceable"><code>sub-SELECT</code></em> )21 } [, ...]22 [ WHERE <em class="replaceable"><code>condition</code></em> ]23</pre></div><div class="refsect1" id="id-1.9.3.152.5"><h2>Description</h2><p>24 <code class="command">INSERT</code> inserts new rows into a table.25 One can insert one or more rows specified by value expressions,26 or zero or more rows resulting from a query.27 </p><p>28 The target column names can be listed in any order. If no list of29 column names is given at all, the default is all the columns of the30 table in their declared order; or the first <em class="replaceable"><code>N</code></em> column31 names, if there are only <em class="replaceable"><code>N</code></em> columns supplied by the32 <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em>. The values33 supplied by the <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em> are34 associated with the explicit or implicit column list left-to-right.35 </p><p>36 Each column not present in the explicit or implicit column list will be37 filled with a default value, either its declared default value38 or null if there is none.39 </p><p>40 If the expression for any column is not of the correct data type,41 automatic type conversion will be attempted.42 </p><p>43 <code class="command">INSERT</code> into tables that lack unique indexes will44 not be blocked by concurrent activity. Tables with unique indexes45 might block if concurrent sessions perform actions that lock or modify46 rows matching the unique index values being inserted; the details47 are covered in <a class="xref" href="index-unique-checks.html" title="64.5. Index Uniqueness Checks">Section 64.5</a>.48 <code class="literal">ON CONFLICT</code> can be used to specify an alternative49 action to raising a unique constraint or exclusion constraint50 violation error. (See <a class="xref" href="sql-insert.html#SQL-ON-CONFLICT" title="ON CONFLICT Clause">ON CONFLICT Clause</a> below.)51 </p><p>52 The optional <code class="literal">RETURNING</code> clause causes <code class="command">INSERT</code>53 to compute and return value(s) based on each row actually inserted54 (or updated, if an <code class="literal">ON CONFLICT DO UPDATE</code> clause was55 used). This is primarily useful for obtaining values that were56 supplied by defaults, such as a serial sequence number. However,57 any expression using the table's columns is allowed. The syntax of58 the <code class="literal">RETURNING</code> list is identical to that of the output59 list of <code class="command">SELECT</code>. Only rows that were successfully60 inserted or updated will be returned. For example, if a row was61 locked but not updated because an <code class="literal">ON CONFLICT DO UPDATE62 ... WHERE</code> clause <em class="replaceable"><code>condition</code></em> was not satisfied, the63 row will not be returned.64 </p><p>65 You must have <code class="literal">INSERT</code> privilege on a table in66 order to insert into it. If <code class="literal">ON CONFLICT DO UPDATE</code> is67 present, <code class="literal">UPDATE</code> privilege on the table is also68 required.69 </p><p>70 If a column list is specified, you only need71 <code class="literal">INSERT</code> privilege on the listed columns.72 Similarly, when <code class="literal">ON CONFLICT DO UPDATE</code> is specified, you73 only need <code class="literal">UPDATE</code> privilege on the column(s) that are74 listed to be updated. However, <code class="literal">ON CONFLICT DO UPDATE</code>75 also requires <code class="literal">SELECT</code> privilege on any column whose76 values are read in the <code class="literal">ON CONFLICT DO UPDATE</code>77 expressions or <em class="replaceable"><code>condition</code></em>.78 </p><p>79 Use of the <code class="literal">RETURNING</code> clause requires <code class="literal">SELECT</code>80 privilege on all columns mentioned in <code class="literal">RETURNING</code>.81 If you use the <em class="replaceable"><code>query</code></em> clause to insert rows from a82 query, you of course need to have <code class="literal">SELECT</code> privilege on83 any table or column used in the query.84 </p></div><div class="refsect1" id="id-1.9.3.152.6"><h2>Parameters</h2><div class="refsect2" id="id-1.9.3.152.6.2"><h3>Inserting</h3><p>85 This section covers parameters that may be used when only86 inserting new rows. Parameters <span class="emphasis"><em>exclusively</em></span>87 used with the <code class="literal">ON CONFLICT</code> clause are described88 separately.89 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>with_query</code></em></span></dt><dd><p>90 The <code class="literal">WITH</code> clause allows you to specify one or more91 subqueries that can be referenced by name in the <code class="command">INSERT</code>92 query. See <a class="xref" href="queries-with.html" title="7.8. WITH Queries (Common Table Expressions)">Section 7.8</a> and <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>93 for details.94 </p><p>95 It is possible for the <em class="replaceable"><code>query</code></em>96 (<code class="command">SELECT</code> statement)97 to also contain a <code class="literal">WITH</code> clause. In such a case both98 sets of <em class="replaceable"><code>with_query</code></em> can be referenced within99 the <em class="replaceable"><code>query</code></em>, but the100 second one takes precedence since it is more closely nested.101 </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>102 The name (optionally schema-qualified) of an existing table.103 </p></dd><dt><span class="term"><em class="replaceable"><code>alias</code></em></span></dt><dd><p>104 A substitute name for <em class="replaceable"><code>table_name</code></em>. When an alias is105 provided, it completely hides the actual name of the table.106 This is particularly useful when <code class="literal">ON CONFLICT DO UPDATE</code>107 targets a table named <code class="varname">excluded</code>, since that will otherwise108 be taken as the name of the special table representing the row proposed109 for insertion.110 </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>111 The name of a column in the table named by <em class="replaceable"><code>table_name</code></em>. The column name112 can be qualified with a subfield name or array subscript, if113 needed. (Inserting into only some fields of a composite114 column leaves the other fields null.) When referencing a115 column with <code class="literal">ON CONFLICT DO UPDATE</code>, do not include116 the table's name in the specification of a target column. For117 example, <code class="literal">INSERT INTO table_name ... ON CONFLICT DO UPDATE118 SET table_name.col = 1</code> is invalid (this follows the general119 behavior for <code class="command">UPDATE</code>).120 </p></dd><dt><span class="term"><code class="literal">OVERRIDING SYSTEM VALUE</code></span></dt><dd><p>121 If this clause is specified, then any values supplied for identity122 columns will override the default sequence-generated values.123 </p><p>124 For an identity column defined as <code class="literal">GENERATED ALWAYS</code>,125 it is an error to insert an explicit value (other than126 <code class="literal">DEFAULT</code>) without specifying either127 <code class="literal">OVERRIDING SYSTEM VALUE</code> or <code class="literal">OVERRIDING USER128 VALUE</code>. (For an identity column defined as129 <code class="literal">GENERATED BY DEFAULT</code>, <code class="literal">OVERRIDING SYSTEM130 VALUE</code> is the normal behavior and specifying it does nothing,131 but <span class="productname">PostgreSQL</span> allows it as an extension.)132 </p></dd><dt><span class="term"><code class="literal">OVERRIDING USER VALUE</code></span></dt><dd><p>133 If this clause is specified, then any values supplied for identity134 columns are ignored and the default sequence-generated values are135 applied.136 </p><p>137 This clause is useful for example when copying values between tables.138 Writing <code class="literal">INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM139 tbl1</code> will copy from <code class="literal">tbl1</code> all columns that140 are not identity columns in <code class="literal">tbl2</code> while values for141 the identity columns in <code class="literal">tbl2</code> will be generated by142 the sequences associated with <code class="literal">tbl2</code>.143 </p></dd><dt><span class="term"><code class="literal">DEFAULT VALUES</code></span></dt><dd><p>144 All columns will be filled with their default values, as if145 <code class="literal">DEFAULT</code> were explicitly specified for each column.146 (An <code class="literal">OVERRIDING</code> clause is not permitted in this147 form.)148 </p></dd><dt><span class="term"><em class="replaceable"><code>expression</code></em></span></dt><dd><p>149 An expression or value to assign to the corresponding column.150 </p></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p>151 The corresponding column will be filled with its default value. An152 identity column will be filled with a new value generated by the153 associated sequence. For a generated column, specifying this is154 permitted but merely specifies the normal behavior of computing the155 column from its generation expression.156 </p></dd><dt><span class="term"><em class="replaceable"><code>query</code></em></span></dt><dd><p>157 A query (<code class="command">SELECT</code> statement) that supplies the158 rows to be inserted. Refer to the159 <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>160 statement for a description of the syntax.161 </p></dd><dt><span class="term"><em class="replaceable"><code>output_expression</code></em></span></dt><dd><p>162 An expression to be computed and returned by the163 <code class="command">INSERT</code> command after each row is inserted or164 updated. The expression can use any column names of the table165 named by <em class="replaceable"><code>table_name</code></em>. Write166 <code class="literal">*</code> to return all columns of the inserted or updated167 row(s).168 </p></dd><dt><span class="term"><em class="replaceable"><code>output_name</code></em></span></dt><dd><p>169 A name to use for a returned column.170 </p></dd></dl></div></div><div class="refsect2" id="SQL-ON-CONFLICT"><h3><code class="literal">ON CONFLICT</code> Clause</h3><a id="id-1.9.3.152.6.3.2" class="indexterm"></a><a id="id-1.9.3.152.6.3.3" class="indexterm"></a><p>171 The optional <code class="literal">ON CONFLICT</code> clause specifies an172 alternative action to raising a unique violation or exclusion173 constraint violation error. For each individual row proposed for174 insertion, either the insertion proceeds, or, if an175 <span class="emphasis"><em>arbiter</em></span> constraint or index specified by176 <em class="parameter"><code>conflict_target</code></em> is violated, the177 alternative <em class="parameter"><code>conflict_action</code></em> is taken.178 <code class="literal">ON CONFLICT DO NOTHING</code> simply avoids inserting179 a row as its alternative action. <code class="literal">ON CONFLICT DO180 UPDATE</code> updates the existing row that conflicts with the181 row proposed for insertion as its alternative action.182 </p><p>183 <em class="parameter"><code>conflict_target</code></em> can perform184 <span class="emphasis"><em>unique index inference</em></span>. When performing185 inference, it consists of one or more <em class="replaceable"><code>index_column_name</code></em> columns and/or186 <em class="replaceable"><code>index_expression</code></em>187 expressions, and an optional <em class="replaceable"><code>index_predicate</code></em>. All <em class="replaceable"><code>table_name</code></em> unique indexes that,188 without regard to order, contain exactly the189 <em class="parameter"><code>conflict_target</code></em>-specified190 columns/expressions are inferred (chosen) as arbiter indexes. If191 an <em class="replaceable"><code>index_predicate</code></em> is192 specified, it must, as a further requirement for inference,193 satisfy arbiter indexes. Note that this means a non-partial194 unique index (a unique index without a predicate) will be inferred195 (and thus used by <code class="literal">ON CONFLICT</code>) if such an index196 satisfying every other criteria is available. If an attempt at197 inference is unsuccessful, an error is raised.198 </p><p>199 <code class="literal">ON CONFLICT DO UPDATE</code> guarantees an atomic200 <code class="command">INSERT</code> or <code class="command">UPDATE</code> outcome;201 provided there is no independent error, one of those two outcomes202 is guaranteed, even under high concurrency. This is also known as203 <em class="firstterm">UPSERT</em> — <span class="quote">“<span class="quote">UPDATE or204 INSERT</span>”</span>.205 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>conflict_target</code></em></span></dt><dd><p>206 Specifies which conflicts <code class="literal">ON CONFLICT</code> takes207 the alternative action on by choosing <em class="firstterm">arbiter208 indexes</em>. Either performs <span class="emphasis"><em>unique index209 inference</em></span>, or names a constraint explicitly. For210 <code class="literal">ON CONFLICT DO NOTHING</code>, it is optional to211 specify a <em class="parameter"><code>conflict_target</code></em>; when212 omitted, conflicts with all usable constraints (and unique213 indexes) are handled. For <code class="literal">ON CONFLICT DO214 UPDATE</code>, a <em class="parameter"><code>conflict_target</code></em>215 <span class="emphasis"><em>must</em></span> be provided.216 </p></dd><dt><span class="term"><em class="replaceable"><code>conflict_action</code></em></span></dt><dd><p>217 <em class="parameter"><code>conflict_action</code></em> specifies an218 alternative <code class="literal">ON CONFLICT</code> action. It can be219 either <code class="literal">DO NOTHING</code>, or a <code class="literal">DO220 UPDATE</code> clause specifying the exact details of the221 <code class="literal">UPDATE</code> action to be performed in case of a222 conflict. The <code class="literal">SET</code> and223 <code class="literal">WHERE</code> clauses in <code class="literal">ON CONFLICT DO224 UPDATE</code> have access to the existing row using the225 table's name (or an alias), and to the row proposed for insertion226 using the special <code class="varname">excluded</code> table.227 <code class="literal">SELECT</code> privilege is required on any column in the228 target table where corresponding <code class="varname">excluded</code>229 columns are read.230 </p><p>231 Note that the effects of all per-row <code class="literal">BEFORE232 INSERT</code> triggers are reflected in233 <code class="varname">excluded</code> values, since those effects may234 have contributed to the row being excluded from insertion.235 </p></dd><dt><span class="term"><em class="replaceable"><code>index_column_name</code></em></span></dt><dd><p>236 The name of a <em class="replaceable"><code>table_name</code></em> column. Used to237 infer arbiter indexes. Follows <code class="command">CREATE238 INDEX</code> format. <code class="literal">SELECT</code> privilege on239 <em class="replaceable"><code>index_column_name</code></em>240 is required.241 </p></dd><dt><span class="term"><em class="replaceable"><code>index_expression</code></em></span></dt><dd><p>242 Similar to <em class="replaceable"><code>index_column_name</code></em>, but used to243 infer expressions on <em class="replaceable"><code>table_name</code></em> columns appearing244 within index definitions (not simple columns). Follows245 <code class="command">CREATE INDEX</code> format. <code class="literal">SELECT</code>246 privilege on any column appearing within <em class="replaceable"><code>index_expression</code></em> is required.247 </p></dd><dt><span class="term"><em class="replaceable"><code>collation</code></em></span></dt><dd><p>248 When specified, mandates that corresponding <em class="replaceable"><code>index_column_name</code></em> or249 <em class="replaceable"><code>index_expression</code></em>250 use a particular collation in order to be matched during251 inference. Typically this is omitted, as collations usually252 do not affect whether or not a constraint violation occurs.253 Follows <code class="command">CREATE INDEX</code> format.254 </p></dd><dt><span class="term"><em class="replaceable"><code>opclass</code></em></span></dt><dd><p>255 When specified, mandates that corresponding <em class="replaceable"><code>index_column_name</code></em> or256 <em class="replaceable"><code>index_expression</code></em>257 use particular operator class in order to be matched during258 inference. Typically this is omitted, as the259 <span class="emphasis"><em>equality</em></span> semantics are often equivalent260 across a type's operator classes anyway, or because it's261 sufficient to trust that the defined unique indexes have the262 pertinent definition of equality. Follows <code class="command">CREATE263 INDEX</code> format.264 </p></dd><dt><span class="term"><em class="replaceable"><code>index_predicate</code></em></span></dt><dd><p>265 Used to allow inference of partial unique indexes. Any266 indexes that satisfy the predicate (which need not actually be267 partial indexes) can be inferred. Follows <code class="command">CREATE268 INDEX</code> format. <code class="literal">SELECT</code> privilege on any269 column appearing within <em class="replaceable"><code>index_predicate</code></em> is required.270 </p></dd><dt><span class="term"><em class="replaceable"><code>constraint_name</code></em></span></dt><dd><p>271 Explicitly specifies an arbiter272 <span class="emphasis"><em>constraint</em></span> by name, rather than inferring273 a constraint or index.274 </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>275 An expression that returns a value of type276 <code class="type">boolean</code>. Only rows for which this expression277 returns <code class="literal">true</code> will be updated, although all278 rows will be locked when the <code class="literal">ON CONFLICT DO UPDATE</code>279 action is taken. Note that280 <em class="replaceable"><code>condition</code></em> is evaluated last, after281 a conflict has been identified as a candidate to update.282 </p></dd></dl></div><p>283 Note that exclusion constraints are not supported as arbiters with284 <code class="literal">ON CONFLICT DO UPDATE</code>. In all cases, only285 <code class="literal">NOT DEFERRABLE</code> constraints and unique indexes286 are supported as arbiters.287 </p><p>288 <code class="command">INSERT</code> with an <code class="literal">ON CONFLICT DO UPDATE</code>289 clause is a <span class="quote">“<span class="quote">deterministic</span>”</span> statement. This means290 that the command will not be allowed to affect any single existing291 row more than once; a cardinality violation error will be raised292 when this situation arises. Rows proposed for insertion should293 not duplicate each other in terms of attributes constrained by an294 arbiter index or constraint.295 </p><p>296 Note that it is currently not supported for the297 <code class="literal">ON CONFLICT DO UPDATE</code> clause of an298 <code class="command">INSERT</code> applied to a partitioned table to update the299 partition key of a conflicting row such that it requires the row be moved300 to a new partition.301 </p><div class="tip"><h3 class="title">Tip</h3><p>302 It is often preferable to use unique index inference rather than303 naming a constraint directly using <code class="literal">ON CONFLICT ON304 CONSTRAINT</code> <em class="replaceable"><code>305 constraint_name</code></em>. Inference will continue to work306 correctly when the underlying index is replaced by another more307 or less equivalent index in an overlapping way, for example when308 using <code class="literal">CREATE UNIQUE INDEX ... CONCURRENTLY</code>309 before dropping the index being replaced.310 </p></div></div></div><div class="refsect1" id="id-1.9.3.152.7"><h2>Outputs</h2><p>311 On successful completion, an <code class="command">INSERT</code> command returns a command312 tag of the form313</p><pre class="screen">314INSERT <em class="replaceable"><code>oid</code></em> <em class="replaceable"><code>count</code></em>315</pre><p>316 The <em class="replaceable"><code>count</code></em> is the number of317 rows inserted or updated. <em class="replaceable"><code>oid</code></em> is always 0 (it318 used to be the <acronym class="acronym">OID</acronym> assigned to the inserted row if319 <em class="replaceable"><code>count</code></em> was exactly one and the target table was320 declared <code class="literal">WITH OIDS</code> and 0 otherwise, but creating a table321 <code class="literal">WITH OIDS</code> is not supported anymore).322 </p><p>323 If the <code class="command">INSERT</code> command contains a <code class="literal">RETURNING</code>324 clause, the result will be similar to that of a <code class="command">SELECT</code>325 statement containing the columns and values defined in the326 <code class="literal">RETURNING</code> list, computed over the row(s) inserted or327 updated by the command.328 </p></div><div class="refsect1" id="id-1.9.3.152.8"><h2>Notes</h2><p>329 If the specified table is a partitioned table, each row is routed to330 the appropriate partition and inserted into it. If the specified table331 is a partition, an error will occur if one of the input rows violates332 the partition constraint.333 </p><p>334 You may also wish to consider using <code class="command">MERGE</code>, since that335 allows mixing <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and336 <code class="command">DELETE</code> within a single statement.337 See <a class="xref" href="sql-merge.html" title="MERGE"><span class="refentrytitle">MERGE</span></a>.338 </p></div><div class="refsect1" id="id-1.9.3.152.9"><h2>Examples</h2><p>339 Insert a single row into table <code class="literal">films</code>:340 341</p><pre class="programlisting">342INSERT INTO films VALUES343 ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');344</pre><p>345 </p><p>346 In this example, the <code class="literal">len</code> column is347 omitted and therefore it will have the default value:348 349</p><pre class="programlisting">350INSERT INTO films (code, title, did, date_prod, kind)351 VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');352</pre><p>353 </p><p>354 This example uses the <code class="literal">DEFAULT</code> clause for355 the date columns rather than specifying a value:356 357</p><pre class="programlisting">358INSERT INTO films VALUES359 ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');360INSERT INTO films (code, title, did, date_prod, kind)361 VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');362</pre><p>363 </p><p>364 To insert a row consisting entirely of default values:365 366</p><pre class="programlisting">367INSERT INTO films DEFAULT VALUES;368</pre><p>369 </p><p>370 To insert multiple rows using the multirow <code class="command">VALUES</code> syntax:371 372</p><pre class="programlisting">373INSERT INTO films (code, title, did, date_prod, kind) VALUES374 ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),375 ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');376</pre><p>377 </p><p>378 This example inserts some rows into table379 <code class="literal">films</code> from a table <code class="literal">tmp_films</code>380 with the same column layout as <code class="literal">films</code>:381 382</p><pre class="programlisting">383INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';384</pre><p>385 </p><p>386 This example inserts into array columns:387 388</p><pre class="programlisting">389-- Create an empty 3x3 gameboard for noughts-and-crosses390INSERT INTO tictactoe (game, board[1:3][1:3])391 VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');392-- The subscripts in the above example aren't really needed393INSERT INTO tictactoe (game, board)394 VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');395</pre><p>396 </p><p>397 Insert a single row into table <code class="literal">distributors</code>, returning398 the sequence number generated by the <code class="literal">DEFAULT</code> clause:399 400</p><pre class="programlisting">401INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')402 RETURNING did;403</pre><p>404 </p><p>405 Increment the sales count of the salesperson who manages the406 account for Acme Corporation, and record the whole updated row407 along with current time in a log table:408</p><pre class="programlisting">409WITH upd AS (410 UPDATE employees SET sales_count = sales_count + 1 WHERE id =411 (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')412 RETURNING *413)414INSERT INTO employees_log SELECT *, current_timestamp FROM upd;415</pre><p>416 </p><p>417 Insert or update new distributors as appropriate. Assumes a unique418 index has been defined that constrains values appearing in the419 <code class="literal">did</code> column. Note that the special420 <code class="varname">excluded</code> table is used to reference values originally421 proposed for insertion:422</p><pre class="programlisting">423INSERT INTO distributors (did, dname)424 VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')425 ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;426</pre><p>427 </p><p>428 Insert a distributor, or do nothing for rows proposed for insertion429 when an existing, excluded row (a row with a matching constrained430 column or columns after before row insert triggers fire) exists.431 Example assumes a unique index has been defined that constrains432 values appearing in the <code class="literal">did</code> column:433</p><pre class="programlisting">434INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')435 ON CONFLICT (did) DO NOTHING;436</pre><p>437 </p><p>438 Insert or update new distributors as appropriate. Example assumes439 a unique index has been defined that constrains values appearing in440 the <code class="literal">did</code> column. <code class="literal">WHERE</code> clause is441 used to limit the rows actually updated (any existing row not442 updated will still be locked, though):443</p><pre class="programlisting">444-- Don't update existing distributors based in a certain ZIP code445INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')446 ON CONFLICT (did) DO UPDATE447 SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'448 WHERE d.zipcode <> '21201';449 450-- Name a constraint directly in the statement (uses associated451-- index to arbitrate taking the DO NOTHING action)452INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')453 ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;454</pre><p>455 </p><p>456 Insert new distributor if possible; otherwise457 <code class="literal">DO NOTHING</code>. Example assumes a unique index has been458 defined that constrains values appearing in the459 <code class="literal">did</code> column on a subset of rows where the460 <code class="literal">is_active</code> Boolean column evaluates to461 <code class="literal">true</code>:462</p><pre class="programlisting">463-- This statement could infer a partial unique index on "did"464-- with a predicate of "WHERE is_active", but it could also465-- just use a regular unique constraint on "did"466INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')467 ON CONFLICT (did) WHERE is_active DO NOTHING;468</pre></div><div class="refsect1" id="id-1.9.3.152.10"><h2>Compatibility</h2><p>469 <code class="command">INSERT</code> conforms to the SQL standard, except that470 the <code class="literal">RETURNING</code> clause is a471 <span class="productname">PostgreSQL</span> extension, as is the ability472 to use <code class="literal">WITH</code> with <code class="command">INSERT</code>, and the ability to473 specify an alternative action with <code class="literal">ON CONFLICT</code>.474 Also, the case in475 which a column name list is omitted, but not all the columns are476 filled from the <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em>,477 is disallowed by the standard. If you prefer a more SQL standard478 conforming statement than <code class="literal">ON CONFLICT</code>, see479 <a class="xref" href="sql-merge.html" title="MERGE"><span class="refentrytitle">MERGE</span></a>.480 </p><p>481 The SQL standard specifies that <code class="literal">OVERRIDING SYSTEM VALUE</code>482 can only be specified if an identity column that is generated always483 exists. PostgreSQL allows the clause in any case and ignores it if it is484 not applicable.485 </p><p>486 Possible limitations of the <em class="replaceable"><code>query</code></em> clause are documented under487 <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>.488 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-importforeignschema.html" title="IMPORT FOREIGN SCHEMA">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-listen.html" title="LISTEN">Next</a></td></tr><tr><td width="40%" align="left" valign="top">IMPORT FOREIGN SCHEMA </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> LISTEN</td></tr></table></div></body></html>