Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-insert.html488 linesDownload Raw Back to 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 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 &lt; '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 &lt;&gt; '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>
codekingpro/portable-devtools · Team Ai