begriffs open source - ai-pg/blob - full-docs/src/sgml/html/sql-insert.html

   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 18.0 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> [, ...] ]
   4 INSERT 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 [ WITH ( { OLD | NEW } AS <em class="replaceable"><code>output_alias</code></em> [, ...] ) ]
   9                 { * | <em class="replaceable"><code>output_expression</code></em> [ [ AS ] <em class="replaceable"><code>output_name</code></em> ] } [, ...] ]
  10
  11 <span class="phrase">where <em class="replaceable"><code>conflict_target</code></em> can be one of:</span>
  12
  13     ( { <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> ]
  14     ON CONSTRAINT <em class="replaceable"><code>constraint_name</code></em>
  15
  16 <span class="phrase">and <em class="replaceable"><code>conflict_action</code></em> is one of:</span>
  17
  18     DO NOTHING
  19     DO UPDATE SET { <em class="replaceable"><code>column_name</code></em> = { <em class="replaceable"><code>expression</code></em> | DEFAULT } |
  20                     ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = [ ROW ] ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) |
  21                     ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = ( <em class="replaceable"><code>sub-SELECT</code></em> )
  22                   } [, ...]
  23               [ WHERE <em class="replaceable"><code>condition</code></em> ]
  24 </pre></div><div class="refsect1" id="id-1.9.3.152.5"><h2>Description</h2><p>
  25    <code class="command">INSERT</code> inserts new rows into a table.
  26    One can insert one or more rows specified by value expressions,
  27    or zero or more rows resulting from a query.
  28   </p><p>
  29    The target column names can be listed in any order.  If no list of
  30    column names is given at all, the default is all the columns of the
  31    table in their declared order; or the first <em class="replaceable"><code>N</code></em> column
  32    names, if there are only <em class="replaceable"><code>N</code></em> columns supplied by the
  33    <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em>.  The values
  34    supplied by the <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em> are
  35    associated with the explicit or implicit column list left-to-right.
  36   </p><p>
  37    Each column not present in the explicit or implicit column list will be
  38    filled with a default value, either its declared default value
  39    or null if there is none.
  40   </p><p>
  41    If the expression for any column is not of the correct data type,
  42    automatic type conversion will be attempted.
  43   </p><p>
  44    <code class="command">INSERT</code> into tables that lack unique indexes will
  45    not be blocked by concurrent activity.  Tables with unique indexes
  46    might block if concurrent sessions perform actions that lock or modify
  47    rows matching the unique index values being inserted;  the details
  48    are covered in <a class="xref" href="index-unique-checks.html" title="63.5. Index Uniqueness Checks">Section 63.5</a>.
  49    <code class="literal">ON CONFLICT</code> can be used to specify an alternative
  50    action to raising a unique constraint or exclusion constraint
  51    violation error. (See <a class="xref" href="sql-insert.html#SQL-ON-CONFLICT" title="ON CONFLICT Clause">ON CONFLICT Clause</a> below.)
  52   </p><p>
  53    The optional <code class="literal">RETURNING</code> clause causes <code class="command">INSERT</code>
  54    to compute and return value(s) based on each row actually inserted
  55    (or updated, if an <code class="literal">ON CONFLICT DO UPDATE</code> clause was
  56    used).  This is primarily useful for obtaining values that were
  57    supplied by defaults, such as a serial sequence number.  However,
  58    any expression using the table's columns is allowed.  The syntax of
  59    the <code class="literal">RETURNING</code> list is identical to that of the output
  60    list of <code class="command">SELECT</code>.  Only rows that were successfully
  61    inserted or updated will be returned.  For example, if a row was
  62    locked but not updated because an <code class="literal">ON CONFLICT DO UPDATE
  63    ... WHERE</code> clause <em class="replaceable"><code>condition</code></em> was not satisfied, the
  64    row will not be returned.
  65   </p><p>
  66    You must have <code class="literal">INSERT</code> privilege on a table in
  67    order to insert into it.  If <code class="literal">ON CONFLICT DO UPDATE</code> is
  68    present, <code class="literal">UPDATE</code> privilege on the table is also
  69    required.
  70   </p><p>
  71    If a column list is specified, you only need
  72    <code class="literal">INSERT</code> privilege on the listed columns.
  73    Similarly, when <code class="literal">ON CONFLICT DO UPDATE</code> is specified, you
  74    only need <code class="literal">UPDATE</code> privilege on the column(s) that are
  75    listed to be updated.  However, <code class="literal">ON CONFLICT DO UPDATE</code>
  76    also requires <code class="literal">SELECT</code> privilege on any column whose
  77    values are read in the <code class="literal">ON CONFLICT DO UPDATE</code>
  78    expressions or <em class="replaceable"><code>condition</code></em>.
  79   </p><p>
  80    Use of the <code class="literal">RETURNING</code> clause requires <code class="literal">SELECT</code>
  81    privilege on all columns mentioned in <code class="literal">RETURNING</code>.
  82    If you use the <em class="replaceable"><code>query</code></em> clause to insert rows from a
  83    query, you of course need to have <code class="literal">SELECT</code> privilege on
  84    any table or column used in the query.
  85   </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>
  86     This section covers parameters that may be used when only
  87     inserting new rows.  Parameters <span class="emphasis"><em>exclusively</em></span>
  88     used with the <code class="literal">ON CONFLICT</code> clause are described
  89     separately.
  90    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>with_query</code></em></span></dt><dd><p>
  91         The <code class="literal">WITH</code> clause allows you to specify one or more
  92         subqueries that can be referenced by name in the <code class="command">INSERT</code>
  93         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>
  94         for details.
  95        </p><p>
  96         It is possible for the <em class="replaceable"><code>query</code></em>
  97         (<code class="command">SELECT</code> statement)
  98         to also contain a <code class="literal">WITH</code> clause.  In such a case both
  99         sets of <em class="replaceable"><code>with_query</code></em> can be referenced within
 100         the <em class="replaceable"><code>query</code></em>, but the
 101         second one takes precedence since it is more closely nested.
 102        </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p>
 103         The name (optionally schema-qualified) of an existing table.
 104        </p></dd><dt><span class="term"><em class="replaceable"><code>alias</code></em></span></dt><dd><p>
 105         A substitute name for <em class="replaceable"><code>table_name</code></em>.  When an alias is
 106         provided, it completely hides the actual name of the table.
 107         This is particularly useful when <code class="literal">ON CONFLICT DO UPDATE</code>
 108         targets a table named <code class="varname">excluded</code>, since that will otherwise
 109         be taken as the name of the special table representing the row proposed
 110         for insertion.
 111        </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>
 112         The name of a column in the table named by <em class="replaceable"><code>table_name</code></em>.  The column name
 113         can be qualified with a subfield name or array subscript, if
 114         needed.  (Inserting into only some fields of a composite
 115         column leaves the other fields null.)  When referencing a
 116         column with <code class="literal">ON CONFLICT DO UPDATE</code>, do not include
 117         the table's name in the specification of a target column.  For
 118         example, <code class="literal">INSERT INTO table_name ... ON CONFLICT DO UPDATE
 119         SET table_name.col = 1</code> is invalid (this follows the general
 120         behavior for <code class="command">UPDATE</code>).
 121        </p></dd><dt><span class="term"><code class="literal">OVERRIDING SYSTEM VALUE</code></span></dt><dd><p>
 122         If this clause is specified, then any values supplied for identity
 123         columns will override the default sequence-generated values.
 124        </p><p>
 125         For an identity column defined as <code class="literal">GENERATED ALWAYS</code>,
 126         it is an error to insert an explicit value (other than
 127         <code class="literal">DEFAULT</code>) without specifying either
 128         <code class="literal">OVERRIDING SYSTEM VALUE</code> or <code class="literal">OVERRIDING USER
 129         VALUE</code>.  (For an identity column defined as
 130         <code class="literal">GENERATED BY DEFAULT</code>, <code class="literal">OVERRIDING SYSTEM
 131         VALUE</code> is the normal behavior and specifying it does nothing,
 132         but <span class="productname">PostgreSQL</span> allows it as an extension.)
 133        </p></dd><dt><span class="term"><code class="literal">OVERRIDING USER VALUE</code></span></dt><dd><p>
 134         If this clause is specified, then any values supplied for identity
 135         columns are ignored and the default sequence-generated values are
 136         applied.
 137        </p><p>
 138         This clause is useful for example when copying values between tables.
 139         Writing <code class="literal">INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM
 140         tbl1</code> will copy from <code class="literal">tbl1</code> all columns that
 141         are not identity columns in <code class="literal">tbl2</code> while values for
 142         the identity columns in <code class="literal">tbl2</code> will be generated by
 143         the sequences associated with <code class="literal">tbl2</code>.
 144        </p></dd><dt><span class="term"><code class="literal">DEFAULT VALUES</code></span></dt><dd><p>
 145         All columns will be filled with their default values, as if
 146         <code class="literal">DEFAULT</code> were explicitly specified for each column.
 147         (An <code class="literal">OVERRIDING</code> clause is not permitted in this
 148         form.)
 149        </p></dd><dt><span class="term"><em class="replaceable"><code>expression</code></em></span></dt><dd><p>
 150         An expression or value to assign to the corresponding column.
 151        </p></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p>
 152         The corresponding column will be filled with its default value.  An
 153         identity column will be filled with a new value generated by the
 154         associated sequence.  For a generated column, specifying this is
 155         permitted but merely specifies the normal behavior of computing the
 156         column from its generation expression.
 157        </p></dd><dt><span class="term"><em class="replaceable"><code>query</code></em></span></dt><dd><p>
 158         A query (<code class="command">SELECT</code> statement) that supplies the
 159         rows to be inserted.  Refer to the
 160         <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>
 161         statement for a description of the syntax.
 162        </p></dd><dt><span class="term"><em class="replaceable"><code>output_alias</code></em></span></dt><dd><p>
 163         An optional substitute name for <code class="literal">OLD</code> or
 164         <code class="literal">NEW</code> rows in the <code class="literal">RETURNING</code> list.
 165        </p><p>
 166         By default, old values from the target table can be returned by writing
 167         <code class="literal">OLD.<em class="replaceable"><code>column_name</code></em></code>
 168         or <code class="literal">OLD.*</code>, and new values can be returned by writing
 169         <code class="literal">NEW.<em class="replaceable"><code>column_name</code></em></code>
 170         or <code class="literal">NEW.*</code>.  When an alias is provided, these names are
 171         hidden and the old or new rows must be referred to using the alias.
 172         For example <code class="literal">RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</code>.
 173        </p></dd><dt><span class="term"><em class="replaceable"><code>output_expression</code></em></span></dt><dd><p>
 174         An expression to be computed and returned by the
 175         <code class="command">INSERT</code> command after each row is inserted or
 176         updated. The expression can use any column names of the table
 177         named by <em class="replaceable"><code>table_name</code></em>.  Write
 178         <code class="literal">*</code> to return all columns of the inserted or updated
 179         row(s).
 180        </p><p>
 181         A column name or <code class="literal">*</code> may be qualified using
 182         <code class="literal">OLD</code> or <code class="literal">NEW</code>, or the corresponding
 183         <em class="replaceable"><code>output_alias</code></em> for
 184         <code class="literal">OLD</code> or <code class="literal">NEW</code>, to cause old or new
 185         values to be returned.  An unqualified column name, or
 186         <code class="literal">*</code>, or a column name or <code class="literal">*</code>
 187         qualified using the target table name or alias will return new values.
 188        </p><p>
 189         For a simple <code class="command">INSERT</code>, all old values will be
 190         <code class="literal">NULL</code>.  However, for an <code class="command">INSERT</code>
 191         with an <code class="literal">ON CONFLICT DO UPDATE</code> clause, the old
 192         values may be non-<code class="literal">NULL</code>.
 193        </p></dd><dt><span class="term"><em class="replaceable"><code>output_name</code></em></span></dt><dd><p>
 194         A name to use for a returned column.
 195        </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>
 196     The optional <code class="literal">ON CONFLICT</code> clause specifies an
 197     alternative action to raising a unique violation or exclusion
 198     constraint violation error.  For each individual row proposed for
 199     insertion, either the insertion proceeds, or, if an
 200     <span class="emphasis"><em>arbiter</em></span> constraint or index specified by
 201     <em class="parameter"><code>conflict_target</code></em> is violated, the
 202     alternative <em class="parameter"><code>conflict_action</code></em> is taken.
 203     <code class="literal">ON CONFLICT DO NOTHING</code> simply avoids inserting
 204     a row as its alternative action.  <code class="literal">ON CONFLICT DO
 205     UPDATE</code> updates the existing row that conflicts with the
 206     row proposed for insertion as its alternative action.
 207    </p><p>
 208     <em class="parameter"><code>conflict_target</code></em> can perform
 209     <span class="emphasis"><em>unique index inference</em></span>.  When performing
 210     inference, it consists of one or more <em class="replaceable"><code>index_column_name</code></em> columns and/or
 211     <em class="replaceable"><code>index_expression</code></em>
 212     expressions, and an optional <em class="replaceable"><code>index_predicate</code></em>.  All <em class="replaceable"><code>table_name</code></em> unique indexes that,
 213     without regard to order, contain exactly the
 214     <em class="parameter"><code>conflict_target</code></em>-specified
 215     columns/expressions are inferred (chosen) as arbiter indexes.  If
 216     an <em class="replaceable"><code>index_predicate</code></em> is
 217     specified, it must, as a further requirement for inference,
 218     satisfy arbiter indexes.  Note that this means a non-partial
 219     unique index (a unique index without a predicate) will be inferred
 220     (and thus used by <code class="literal">ON CONFLICT</code>) if such an index
 221     satisfying every other criteria is available.  If an attempt at
 222     inference is unsuccessful, an error is raised.
 223    </p><p>
 224     <code class="literal">ON CONFLICT DO UPDATE</code> guarantees an atomic
 225     <code class="command">INSERT</code> or <code class="command">UPDATE</code> outcome;
 226     provided there is no independent error, one of those two outcomes
 227     is guaranteed, even under high concurrency.  This is also known as
 228     <em class="firstterm">UPSERT</em> — <span class="quote">“<span class="quote">UPDATE or
 229     INSERT</span>”</span>.
 230    </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>conflict_target</code></em></span></dt><dd><p>
 231         Specifies which conflicts <code class="literal">ON CONFLICT</code> takes
 232         the alternative action on by choosing <em class="firstterm">arbiter
 233         indexes</em>.  Either performs <span class="emphasis"><em>unique index
 234         inference</em></span>, or names a constraint explicitly.  For
 235         <code class="literal">ON CONFLICT DO NOTHING</code>, it is optional to
 236         specify a <em class="parameter"><code>conflict_target</code></em>; when
 237         omitted, conflicts with all usable constraints (and unique
 238         indexes) are handled.  For <code class="literal">ON CONFLICT DO
 239         UPDATE</code>, a <em class="parameter"><code>conflict_target</code></em>
 240         <span class="emphasis"><em>must</em></span> be provided.
 241        </p></dd><dt><span class="term"><em class="replaceable"><code>conflict_action</code></em></span></dt><dd><p>
 242         <em class="parameter"><code>conflict_action</code></em> specifies an
 243         alternative <code class="literal">ON CONFLICT</code> action.  It can be
 244         either <code class="literal">DO NOTHING</code>, or a <code class="literal">DO
 245         UPDATE</code> clause specifying the exact details of the
 246         <code class="literal">UPDATE</code> action to be performed in case of a
 247         conflict.  The <code class="literal">SET</code> and
 248         <code class="literal">WHERE</code> clauses in <code class="literal">ON CONFLICT DO
 249         UPDATE</code> have access to the existing row using the
 250         table's name (or an alias), and to the row proposed for insertion
 251         using the special <code class="varname">excluded</code> table.
 252         <code class="literal">SELECT</code> privilege is required on any column in the
 253         target table where corresponding <code class="varname">excluded</code>
 254         columns are read.
 255        </p><p>
 256         Note that the effects of all per-row <code class="literal">BEFORE
 257         INSERT</code> triggers are reflected in
 258         <code class="varname">excluded</code> values, since those effects may
 259         have contributed to the row being excluded from insertion.
 260        </p></dd><dt><span class="term"><em class="replaceable"><code>index_column_name</code></em></span></dt><dd><p>
 261         The name of a <em class="replaceable"><code>table_name</code></em> column.  Used to
 262         infer arbiter indexes.  Follows <code class="command">CREATE
 263         INDEX</code> format.  <code class="literal">SELECT</code> privilege on
 264         <em class="replaceable"><code>index_column_name</code></em>
 265         is required.
 266        </p></dd><dt><span class="term"><em class="replaceable"><code>index_expression</code></em></span></dt><dd><p>
 267         Similar to <em class="replaceable"><code>index_column_name</code></em>, but used to
 268         infer expressions on <em class="replaceable"><code>table_name</code></em> columns appearing
 269         within index definitions (not simple columns).  Follows
 270         <code class="command">CREATE INDEX</code> format.  <code class="literal">SELECT</code>
 271         privilege on any column appearing within <em class="replaceable"><code>index_expression</code></em> is required.
 272        </p></dd><dt><span class="term"><em class="replaceable"><code>collation</code></em></span></dt><dd><p>
 273         When specified, mandates that corresponding <em class="replaceable"><code>index_column_name</code></em> or
 274         <em class="replaceable"><code>index_expression</code></em>
 275         use a particular collation in order to be matched during
 276         inference.  Typically this is omitted, as collations usually
 277         do not affect whether or not a constraint violation occurs.
 278         Follows <code class="command">CREATE INDEX</code> format.
 279        </p></dd><dt><span class="term"><em class="replaceable"><code>opclass</code></em></span></dt><dd><p>
 280         When specified, mandates that corresponding <em class="replaceable"><code>index_column_name</code></em> or
 281         <em class="replaceable"><code>index_expression</code></em>
 282         use particular operator class in order to be matched during
 283         inference.  Typically this is omitted,  as the
 284         <span class="emphasis"><em>equality</em></span> semantics are often equivalent
 285         across a type's operator classes anyway, or because it's
 286         sufficient to trust that the defined unique indexes have the
 287         pertinent definition of equality.  Follows <code class="command">CREATE
 288         INDEX</code> format.
 289        </p></dd><dt><span class="term"><em class="replaceable"><code>index_predicate</code></em></span></dt><dd><p>
 290         Used to allow inference of partial unique indexes.  Any
 291         indexes that satisfy the predicate (which need not actually be
 292         partial indexes) can be inferred.  Follows <code class="command">CREATE
 293         INDEX</code> format.  <code class="literal">SELECT</code> privilege on any
 294         column appearing within <em class="replaceable"><code>index_predicate</code></em> is required.
 295        </p></dd><dt><span class="term"><em class="replaceable"><code>constraint_name</code></em></span></dt><dd><p>
 296         Explicitly specifies an arbiter
 297         <span class="emphasis"><em>constraint</em></span> by name, rather than inferring
 298         a constraint or index.
 299        </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>
 300         An expression that returns a value of type
 301         <code class="type">boolean</code>.  Only rows for which this expression
 302         returns <code class="literal">true</code> will be updated, although all
 303         rows will be locked when the <code class="literal">ON CONFLICT DO UPDATE</code>
 304         action is taken.  Note that
 305         <em class="replaceable"><code>condition</code></em> is evaluated last, after
 306         a conflict has been identified as a candidate to update.
 307        </p></dd></dl></div><p>
 308     Note that exclusion constraints are not supported as arbiters with
 309     <code class="literal">ON CONFLICT DO UPDATE</code>. In all cases, only
 310     <code class="literal">NOT DEFERRABLE</code> constraints and unique indexes
 311     are supported as arbiters.
 312    </p><p>
 313     <code class="command">INSERT</code> with an <code class="literal">ON CONFLICT DO UPDATE</code>
 314     clause is a <span class="quote">“<span class="quote">deterministic</span>”</span> statement.  This means
 315     that the command will not be allowed to affect any single existing
 316     row more than once; a cardinality violation error will be raised
 317     when this situation arises.  Rows proposed for insertion should
 318     not duplicate each other in terms of attributes constrained by an
 319     arbiter index or constraint.
 320    </p><p>
 321     Note that it is currently not supported for the
 322     <code class="literal">ON CONFLICT DO UPDATE</code> clause of an
 323     <code class="command">INSERT</code> applied to a partitioned table to update the
 324     partition key of a conflicting row such that it requires the row be moved
 325     to a new partition.
 326    </p><div class="tip"><h3 class="title">Tip</h3><p>
 327      It is often preferable to use unique index inference rather than
 328      naming a constraint directly using <code class="literal">ON CONFLICT ON
 329      CONSTRAINT</code> <em class="replaceable"><code>
 330      constraint_name</code></em>.  Inference will continue to work
 331      correctly when the underlying index is replaced by another more
 332      or less equivalent index in an overlapping way, for example when
 333      using <code class="literal">CREATE UNIQUE INDEX ...  CONCURRENTLY</code>
 334      before dropping the index being replaced.
 335     </p></div></div></div><div class="refsect1" id="id-1.9.3.152.7"><h2>Outputs</h2><p>
 336    On successful completion, an <code class="command">INSERT</code> command returns a command
 337    tag of the form
 338 </p><pre class="screen">
 339 INSERT <em class="replaceable"><code>oid</code></em> <em class="replaceable"><code>count</code></em>
 340 </pre><p>
 341    The <em class="replaceable"><code>count</code></em> is the number of
 342    rows inserted or updated.  <em class="replaceable"><code>oid</code></em> is always 0 (it
 343    used to be the <acronym class="acronym">OID</acronym> assigned to the inserted row if
 344    <em class="replaceable"><code>count</code></em> was exactly one and the target table was
 345    declared <code class="literal">WITH OIDS</code> and 0 otherwise, but creating a table
 346    <code class="literal">WITH OIDS</code> is not supported anymore).
 347   </p><p>
 348    If the <code class="command">INSERT</code> command contains a <code class="literal">RETURNING</code>
 349    clause, the result will be similar to that of a <code class="command">SELECT</code>
 350    statement containing the columns and values defined in the
 351    <code class="literal">RETURNING</code> list, computed over the row(s) inserted or
 352    updated by the command.
 353   </p></div><div class="refsect1" id="id-1.9.3.152.8"><h2>Notes</h2><p>
 354    If the specified table is a partitioned table, each row is routed to
 355    the appropriate partition and inserted into it.  If the specified table
 356    is a partition, an error will occur if one of the input rows violates
 357    the partition constraint.
 358   </p><p>
 359    You may also wish to consider using <code class="command">MERGE</code>, since that
 360    allows mixing <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and
 361    <code class="command">DELETE</code> within a single statement.
 362    See <a class="xref" href="sql-merge.html" title="MERGE"><span class="refentrytitle">MERGE</span></a>.
 363   </p></div><div class="refsect1" id="id-1.9.3.152.9"><h2>Examples</h2><p>
 364    Insert a single row into table <code class="literal">films</code>:
 365
 366 </p><pre class="programlisting">
 367 INSERT INTO films VALUES
 368     ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');
 369 </pre><p>
 370   </p><p>
 371    In this example, the <code class="literal">len</code> column is
 372    omitted and therefore it will have the default value:
 373
 374 </p><pre class="programlisting">
 375 INSERT INTO films (code, title, did, date_prod, kind)
 376     VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');
 377 </pre><p>
 378   </p><p>
 379    This example uses the <code class="literal">DEFAULT</code> clause for
 380    the date columns rather than specifying a value:
 381
 382 </p><pre class="programlisting">
 383 INSERT INTO films VALUES
 384     ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
 385 INSERT INTO films (code, title, did, date_prod, kind)
 386     VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');
 387 </pre><p>
 388   </p><p>
 389    To insert a row consisting entirely of default values:
 390
 391 </p><pre class="programlisting">
 392 INSERT INTO films DEFAULT VALUES;
 393 </pre><p>
 394   </p><p>
 395    To insert multiple rows using the multirow <code class="command">VALUES</code> syntax:
 396
 397 </p><pre class="programlisting">
 398 INSERT INTO films (code, title, did, date_prod, kind) VALUES
 399     ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
 400     ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');
 401 </pre><p>
 402   </p><p>
 403    This example inserts some rows into table
 404    <code class="literal">films</code> from a table <code class="literal">tmp_films</code>
 405    with the same column layout as <code class="literal">films</code>:
 406
 407 </p><pre class="programlisting">
 408 INSERT INTO films SELECT * FROM tmp_films WHERE date_prod &lt; '2004-05-07';
 409 </pre><p>
 410   </p><p>
 411    This example inserts into array columns:
 412
 413 </p><pre class="programlisting">
 414 -- Create an empty 3x3 gameboard for noughts-and-crosses
 415 INSERT INTO tictactoe (game, board[1:3][1:3])
 416     VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
 417 -- The subscripts in the above example aren't really needed
 418 INSERT INTO tictactoe (game, board)
 419     VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');
 420 </pre><p>
 421   </p><p>
 422    Insert a single row into table <code class="literal">distributors</code>, returning
 423    the sequence number generated by the <code class="literal">DEFAULT</code> clause:
 424
 425 </p><pre class="programlisting">
 426 INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
 427    RETURNING did;
 428 </pre><p>
 429   </p><p>
 430    Increment the sales count of the salesperson who manages the
 431    account for Acme Corporation, and record the whole updated row
 432    along with current time in a log table:
 433 </p><pre class="programlisting">
 434 WITH upd AS (
 435   UPDATE employees SET sales_count = sales_count + 1 WHERE id =
 436     (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
 437     RETURNING *
 438 )
 439 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
 440 </pre><p>
 441   </p><p>
 442    Insert or update new distributors as appropriate.  Assumes a unique
 443    index has been defined that constrains values appearing in the
 444    <code class="literal">did</code> column.  Note that the special
 445    <code class="varname">excluded</code> table is used to reference values originally
 446    proposed for insertion:
 447 </p><pre class="programlisting">
 448 INSERT INTO distributors (did, dname)
 449     VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
 450     ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
 451 </pre><p>
 452   </p><p>
 453    Insert or update new distributors as above, returning information
 454    about any existing values that were updated, together with the new data
 455    inserted.  Note that the returned values for <code class="literal">old_did</code>
 456    and <code class="literal">old_dname</code> will be <code class="literal">NULL</code> for
 457    non-conflicting rows:
 458 </p><pre class="programlisting">
 459 INSERT INTO distributors (did, dname)
 460     VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
 461     ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname
 462     RETURNING old.did AS old_did, old.dname AS old_dname,
 463               new.did AS new_did, new.dname AS new_dname;
 464 </pre><p>
 465   </p><p>
 466    Insert a distributor, or do nothing for rows proposed for insertion
 467    when an existing, excluded row (a row with a matching constrained
 468    column or columns after before row insert triggers fire) exists.
 469    Example assumes a unique index has been defined that constrains
 470    values appearing in the <code class="literal">did</code> column:
 471 </p><pre class="programlisting">
 472 INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
 473     ON CONFLICT (did) DO NOTHING;
 474 </pre><p>
 475   </p><p>
 476    Insert or update new distributors as appropriate.  Example assumes
 477    a unique index has been defined that constrains values appearing in
 478    the <code class="literal">did</code> column.  <code class="literal">WHERE</code> clause is
 479    used to limit the rows actually updated (any existing row not
 480    updated will still be locked, though):
 481 </p><pre class="programlisting">
 482 -- Don't update existing distributors based in a certain ZIP code
 483 INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
 484     ON CONFLICT (did) DO UPDATE
 485     SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
 486     WHERE d.zipcode &lt;&gt; '21201';
 487
 488 -- Name a constraint directly in the statement (uses associated
 489 -- index to arbitrate taking the DO NOTHING action)
 490 INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
 491     ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;
 492 </pre><p>
 493   </p><p>
 494    Insert new distributor if possible;  otherwise
 495    <code class="literal">DO NOTHING</code>.  Example assumes a unique index has been
 496    defined that constrains values appearing in the
 497    <code class="literal">did</code> column on a subset of rows where the
 498    <code class="literal">is_active</code> Boolean column evaluates to
 499    <code class="literal">true</code>:
 500 </p><pre class="programlisting">
 501 -- This statement could infer a partial unique index on "did"
 502 -- with a predicate of "WHERE is_active", but it could also
 503 -- just use a regular unique constraint on "did"
 504 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
 505     ON CONFLICT (did) WHERE is_active DO NOTHING;
 506 </pre></div><div class="refsect1" id="id-1.9.3.152.10"><h2>Compatibility</h2><p>
 507    <code class="command">INSERT</code> conforms to the SQL standard, except that
 508    the <code class="literal">RETURNING</code> clause is a
 509    <span class="productname">PostgreSQL</span> extension, as is the ability
 510    to use <code class="literal">WITH</code> with <code class="command">INSERT</code>, and the ability to
 511    specify an alternative action with <code class="literal">ON CONFLICT</code>.
 512    Also, the case in
 513    which a column name list is omitted, but not all the columns are
 514    filled from the <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em>,
 515    is disallowed by the standard. If you prefer a more SQL standard
 516    conforming statement than <code class="literal">ON CONFLICT</code>, see
 517    <a class="xref" href="sql-merge.html" title="MERGE"><span class="refentrytitle">MERGE</span></a>.
 518   </p><p>
 519    The SQL standard specifies that <code class="literal">OVERRIDING SYSTEM VALUE</code>
 520    can only be specified if an identity column that is generated always
 521    exists.  PostgreSQL allows the clause in any case and ignores it if it is
 522    not applicable.
 523   </p><p>
 524    Possible limitations of the <em class="replaceable"><code>query</code></em> clause are documented under
 525    <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>.
 526   </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 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> LISTEN</td></tr></table></div></body></html>