diff options
Diffstat (limited to 'doc/src/sgml/html/sql-insert.html')
| -rw-r--r-- | doc/src/sgml/html/sql-insert.html | 488 |
1 files changed, 488 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-insert.html b/doc/src/sgml/html/sql-insert.html new file mode 100644 index 0000000..e840ddc --- /dev/null +++ b/doc/src/sgml/html/sql-insert.html @@ -0,0 +1,488 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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 15.5 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"> +[ WITH [ RECURSIVE ] <em class="replaceable"><code>with_query</code></em> [, ...] ] +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> [, ...] ) ] + [ OVERRIDING { SYSTEM | USER } VALUE ] + { DEFAULT VALUES | VALUES ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) [, ...] | <em class="replaceable"><code>query</code></em> } + [ ON CONFLICT [ <em class="replaceable"><code>conflict_target</code></em> ] <em class="replaceable"><code>conflict_action</code></em> ] + [ RETURNING * | <em class="replaceable"><code>output_expression</code></em> [ [ AS ] <em class="replaceable"><code>output_name</code></em> ] [, ...] ] + +<span class="phrase">where <em class="replaceable"><code>conflict_target</code></em> can be one of:</span> + + ( { <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> ] + ON CONSTRAINT <em class="replaceable"><code>constraint_name</code></em> + +<span class="phrase">and <em class="replaceable"><code>conflict_action</code></em> is one of:</span> + + DO NOTHING + DO UPDATE SET { <em class="replaceable"><code>column_name</code></em> = { <em class="replaceable"><code>expression</code></em> | DEFAULT } | + ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = [ ROW ] ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) | + ( <em class="replaceable"><code>column_name</code></em> [, ...] ) = ( <em class="replaceable"><code>sub-SELECT</code></em> ) + } [, ...] + [ WHERE <em class="replaceable"><code>condition</code></em> ] +</pre></div><div class="refsect1" id="id-1.9.3.152.5"><h2>Description</h2><p> + <code class="command">INSERT</code> inserts new rows into a table. + One can insert one or more rows specified by value expressions, + or zero or more rows resulting from a query. + </p><p> + The target column names can be listed in any order. If no list of + column names is given at all, the default is all the columns of the + table in their declared order; or the first <em class="replaceable"><code>N</code></em> column + names, if there are only <em class="replaceable"><code>N</code></em> columns supplied by the + <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em>. The values + supplied by the <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em> are + associated with the explicit or implicit column list left-to-right. + </p><p> + Each column not present in the explicit or implicit column list will be + filled with a default value, either its declared default value + or null if there is none. + </p><p> + If the expression for any column is not of the correct data type, + automatic type conversion will be attempted. + </p><p> + <code class="command">INSERT</code> into tables that lack unique indexes will + not be blocked by concurrent activity. Tables with unique indexes + might block if concurrent sessions perform actions that lock or modify + rows matching the unique index values being inserted; the details + are covered in <a class="xref" href="index-unique-checks.html" title="64.5. Index Uniqueness Checks">Section 64.5</a>. + <code class="literal">ON CONFLICT</code> can be used to specify an alternative + action to raising a unique constraint or exclusion constraint + violation error. (See <a class="xref" href="sql-insert.html#SQL-ON-CONFLICT" title="ON CONFLICT Clause">ON CONFLICT Clause</a> below.) + </p><p> + The optional <code class="literal">RETURNING</code> clause causes <code class="command">INSERT</code> + to compute and return value(s) based on each row actually inserted + (or updated, if an <code class="literal">ON CONFLICT DO UPDATE</code> clause was + used). This is primarily useful for obtaining values that were + supplied by defaults, such as a serial sequence number. However, + any expression using the table's columns is allowed. The syntax of + the <code class="literal">RETURNING</code> list is identical to that of the output + list of <code class="command">SELECT</code>. Only rows that were successfully + inserted or updated will be returned. For example, if a row was + locked but not updated because an <code class="literal">ON CONFLICT DO UPDATE + ... WHERE</code> clause <em class="replaceable"><code>condition</code></em> was not satisfied, the + row will not be returned. + </p><p> + You must have <code class="literal">INSERT</code> privilege on a table in + order to insert into it. If <code class="literal">ON CONFLICT DO UPDATE</code> is + present, <code class="literal">UPDATE</code> privilege on the table is also + required. + </p><p> + If a column list is specified, you only need + <code class="literal">INSERT</code> privilege on the listed columns. + Similarly, when <code class="literal">ON CONFLICT DO UPDATE</code> is specified, you + only need <code class="literal">UPDATE</code> privilege on the column(s) that are + listed to be updated. However, <code class="literal">ON CONFLICT DO UPDATE</code> + also requires <code class="literal">SELECT</code> privilege on any column whose + values are read in the <code class="literal">ON CONFLICT DO UPDATE</code> + expressions or <em class="replaceable"><code>condition</code></em>. + </p><p> + Use of the <code class="literal">RETURNING</code> clause requires <code class="literal">SELECT</code> + privilege on all columns mentioned in <code class="literal">RETURNING</code>. + If you use the <em class="replaceable"><code>query</code></em> clause to insert rows from a + query, you of course need to have <code class="literal">SELECT</code> privilege on + any table or column used in the query. + </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> + This section covers parameters that may be used when only + inserting new rows. Parameters <span class="emphasis"><em>exclusively</em></span> + used with the <code class="literal">ON CONFLICT</code> clause are described + separately. + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>with_query</code></em></span></dt><dd><p> + The <code class="literal">WITH</code> clause allows you to specify one or more + subqueries that can be referenced by name in the <code class="command">INSERT</code> + 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> + for details. + </p><p> + It is possible for the <em class="replaceable"><code>query</code></em> + (<code class="command">SELECT</code> statement) + to also contain a <code class="literal">WITH</code> clause. In such a case both + sets of <em class="replaceable"><code>with_query</code></em> can be referenced within + the <em class="replaceable"><code>query</code></em>, but the + second one takes precedence since it is more closely nested. + </p></dd><dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of an existing table. + </p></dd><dt><span class="term"><em class="replaceable"><code>alias</code></em></span></dt><dd><p> + A substitute name for <em class="replaceable"><code>table_name</code></em>. When an alias is + provided, it completely hides the actual name of the table. + This is particularly useful when <code class="literal">ON CONFLICT DO UPDATE</code> + targets a table named <code class="varname">excluded</code>, since that will otherwise + be taken as the name of the special table representing the row proposed + for insertion. + </p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p> + The name of a column in the table named by <em class="replaceable"><code>table_name</code></em>. The column name + can be qualified with a subfield name or array subscript, if + needed. (Inserting into only some fields of a composite + column leaves the other fields null.) When referencing a + column with <code class="literal">ON CONFLICT DO UPDATE</code>, do not include + the table's name in the specification of a target column. For + example, <code class="literal">INSERT INTO table_name ... ON CONFLICT DO UPDATE + SET table_name.col = 1</code> is invalid (this follows the general + behavior for <code class="command">UPDATE</code>). + </p></dd><dt><span class="term"><code class="literal">OVERRIDING SYSTEM VALUE</code></span></dt><dd><p> + If this clause is specified, then any values supplied for identity + columns will override the default sequence-generated values. + </p><p> + For an identity column defined as <code class="literal">GENERATED ALWAYS</code>, + it is an error to insert an explicit value (other than + <code class="literal">DEFAULT</code>) without specifying either + <code class="literal">OVERRIDING SYSTEM VALUE</code> or <code class="literal">OVERRIDING USER + VALUE</code>. (For an identity column defined as + <code class="literal">GENERATED BY DEFAULT</code>, <code class="literal">OVERRIDING SYSTEM + VALUE</code> is the normal behavior and specifying it does nothing, + but <span class="productname">PostgreSQL</span> allows it as an extension.) + </p></dd><dt><span class="term"><code class="literal">OVERRIDING USER VALUE</code></span></dt><dd><p> + If this clause is specified, then any values supplied for identity + columns are ignored and the default sequence-generated values are + applied. + </p><p> + This clause is useful for example when copying values between tables. + Writing <code class="literal">INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM + tbl1</code> will copy from <code class="literal">tbl1</code> all columns that + are not identity columns in <code class="literal">tbl2</code> while values for + the identity columns in <code class="literal">tbl2</code> will be generated by + the sequences associated with <code class="literal">tbl2</code>. + </p></dd><dt><span class="term"><code class="literal">DEFAULT VALUES</code></span></dt><dd><p> + All columns will be filled with their default values, as if + <code class="literal">DEFAULT</code> were explicitly specified for each column. + (An <code class="literal">OVERRIDING</code> clause is not permitted in this + form.) + </p></dd><dt><span class="term"><em class="replaceable"><code>expression</code></em></span></dt><dd><p> + An expression or value to assign to the corresponding column. + </p></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p> + The corresponding column will be filled with its default value. An + identity column will be filled with a new value generated by the + associated sequence. For a generated column, specifying this is + permitted but merely specifies the normal behavior of computing the + column from its generation expression. + </p></dd><dt><span class="term"><em class="replaceable"><code>query</code></em></span></dt><dd><p> + A query (<code class="command">SELECT</code> statement) that supplies the + rows to be inserted. Refer to the + <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a> + statement for a description of the syntax. + </p></dd><dt><span class="term"><em class="replaceable"><code>output_expression</code></em></span></dt><dd><p> + An expression to be computed and returned by the + <code class="command">INSERT</code> command after each row is inserted or + updated. The expression can use any column names of the table + named by <em class="replaceable"><code>table_name</code></em>. Write + <code class="literal">*</code> to return all columns of the inserted or updated + row(s). + </p></dd><dt><span class="term"><em class="replaceable"><code>output_name</code></em></span></dt><dd><p> + A name to use for a returned column. + </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> + The optional <code class="literal">ON CONFLICT</code> clause specifies an + alternative action to raising a unique violation or exclusion + constraint violation error. For each individual row proposed for + insertion, either the insertion proceeds, or, if an + <span class="emphasis"><em>arbiter</em></span> constraint or index specified by + <em class="parameter"><code>conflict_target</code></em> is violated, the + alternative <em class="parameter"><code>conflict_action</code></em> is taken. + <code class="literal">ON CONFLICT DO NOTHING</code> simply avoids inserting + a row as its alternative action. <code class="literal">ON CONFLICT DO + UPDATE</code> updates the existing row that conflicts with the + row proposed for insertion as its alternative action. + </p><p> + <em class="parameter"><code>conflict_target</code></em> can perform + <span class="emphasis"><em>unique index inference</em></span>. When performing + inference, it consists of one or more <em class="replaceable"><code>index_column_name</code></em> columns and/or + <em class="replaceable"><code>index_expression</code></em> + expressions, and an optional <em class="replaceable"><code>index_predicate</code></em>. All <em class="replaceable"><code>table_name</code></em> unique indexes that, + without regard to order, contain exactly the + <em class="parameter"><code>conflict_target</code></em>-specified + columns/expressions are inferred (chosen) as arbiter indexes. If + an <em class="replaceable"><code>index_predicate</code></em> is + specified, it must, as a further requirement for inference, + satisfy arbiter indexes. Note that this means a non-partial + unique index (a unique index without a predicate) will be inferred + (and thus used by <code class="literal">ON CONFLICT</code>) if such an index + satisfying every other criteria is available. If an attempt at + inference is unsuccessful, an error is raised. + </p><p> + <code class="literal">ON CONFLICT DO UPDATE</code> guarantees an atomic + <code class="command">INSERT</code> or <code class="command">UPDATE</code> outcome; + provided there is no independent error, one of those two outcomes + is guaranteed, even under high concurrency. This is also known as + <em class="firstterm">UPSERT</em> — <span class="quote">“<span class="quote">UPDATE or + INSERT</span>”</span>. + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>conflict_target</code></em></span></dt><dd><p> + Specifies which conflicts <code class="literal">ON CONFLICT</code> takes + the alternative action on by choosing <em class="firstterm">arbiter + indexes</em>. Either performs <span class="emphasis"><em>unique index + inference</em></span>, or names a constraint explicitly. For + <code class="literal">ON CONFLICT DO NOTHING</code>, it is optional to + specify a <em class="parameter"><code>conflict_target</code></em>; when + omitted, conflicts with all usable constraints (and unique + indexes) are handled. For <code class="literal">ON CONFLICT DO + UPDATE</code>, a <em class="parameter"><code>conflict_target</code></em> + <span class="emphasis"><em>must</em></span> be provided. + </p></dd><dt><span class="term"><em class="replaceable"><code>conflict_action</code></em></span></dt><dd><p> + <em class="parameter"><code>conflict_action</code></em> specifies an + alternative <code class="literal">ON CONFLICT</code> action. It can be + either <code class="literal">DO NOTHING</code>, or a <code class="literal">DO + UPDATE</code> clause specifying the exact details of the + <code class="literal">UPDATE</code> action to be performed in case of a + conflict. The <code class="literal">SET</code> and + <code class="literal">WHERE</code> clauses in <code class="literal">ON CONFLICT DO + UPDATE</code> have access to the existing row using the + table's name (or an alias), and to the row proposed for insertion + using the special <code class="varname">excluded</code> table. + <code class="literal">SELECT</code> privilege is required on any column in the + target table where corresponding <code class="varname">excluded</code> + columns are read. + </p><p> + Note that the effects of all per-row <code class="literal">BEFORE + INSERT</code> triggers are reflected in + <code class="varname">excluded</code> values, since those effects may + have contributed to the row being excluded from insertion. + </p></dd><dt><span class="term"><em class="replaceable"><code>index_column_name</code></em></span></dt><dd><p> + The name of a <em class="replaceable"><code>table_name</code></em> column. Used to + infer arbiter indexes. Follows <code class="command">CREATE + INDEX</code> format. <code class="literal">SELECT</code> privilege on + <em class="replaceable"><code>index_column_name</code></em> + is required. + </p></dd><dt><span class="term"><em class="replaceable"><code>index_expression</code></em></span></dt><dd><p> + Similar to <em class="replaceable"><code>index_column_name</code></em>, but used to + infer expressions on <em class="replaceable"><code>table_name</code></em> columns appearing + within index definitions (not simple columns). Follows + <code class="command">CREATE INDEX</code> format. <code class="literal">SELECT</code> + privilege on any column appearing within <em class="replaceable"><code>index_expression</code></em> is required. + </p></dd><dt><span class="term"><em class="replaceable"><code>collation</code></em></span></dt><dd><p> + When specified, mandates that corresponding <em class="replaceable"><code>index_column_name</code></em> or + <em class="replaceable"><code>index_expression</code></em> + use a particular collation in order to be matched during + inference. Typically this is omitted, as collations usually + do not affect whether or not a constraint violation occurs. + Follows <code class="command">CREATE INDEX</code> format. + </p></dd><dt><span class="term"><em class="replaceable"><code>opclass</code></em></span></dt><dd><p> + When specified, mandates that corresponding <em class="replaceable"><code>index_column_name</code></em> or + <em class="replaceable"><code>index_expression</code></em> + use particular operator class in order to be matched during + inference. Typically this is omitted, as the + <span class="emphasis"><em>equality</em></span> semantics are often equivalent + across a type's operator classes anyway, or because it's + sufficient to trust that the defined unique indexes have the + pertinent definition of equality. Follows <code class="command">CREATE + INDEX</code> format. + </p></dd><dt><span class="term"><em class="replaceable"><code>index_predicate</code></em></span></dt><dd><p> + Used to allow inference of partial unique indexes. Any + indexes that satisfy the predicate (which need not actually be + partial indexes) can be inferred. Follows <code class="command">CREATE + INDEX</code> format. <code class="literal">SELECT</code> privilege on any + column appearing within <em class="replaceable"><code>index_predicate</code></em> is required. + </p></dd><dt><span class="term"><em class="replaceable"><code>constraint_name</code></em></span></dt><dd><p> + Explicitly specifies an arbiter + <span class="emphasis"><em>constraint</em></span> by name, rather than inferring + a constraint or index. + </p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p> + An expression that returns a value of type + <code class="type">boolean</code>. Only rows for which this expression + returns <code class="literal">true</code> will be updated, although all + rows will be locked when the <code class="literal">ON CONFLICT DO UPDATE</code> + action is taken. Note that + <em class="replaceable"><code>condition</code></em> is evaluated last, after + a conflict has been identified as a candidate to update. + </p></dd></dl></div><p> + Note that exclusion constraints are not supported as arbiters with + <code class="literal">ON CONFLICT DO UPDATE</code>. In all cases, only + <code class="literal">NOT DEFERRABLE</code> constraints and unique indexes + are supported as arbiters. + </p><p> + <code class="command">INSERT</code> with an <code class="literal">ON CONFLICT DO UPDATE</code> + clause is a <span class="quote">“<span class="quote">deterministic</span>”</span> statement. This means + that the command will not be allowed to affect any single existing + row more than once; a cardinality violation error will be raised + when this situation arises. Rows proposed for insertion should + not duplicate each other in terms of attributes constrained by an + arbiter index or constraint. + </p><p> + Note that it is currently not supported for the + <code class="literal">ON CONFLICT DO UPDATE</code> clause of an + <code class="command">INSERT</code> applied to a partitioned table to update the + partition key of a conflicting row such that it requires the row be moved + to a new partition. + </p><div class="tip"><h3 class="title">Tip</h3><p> + It is often preferable to use unique index inference rather than + naming a constraint directly using <code class="literal">ON CONFLICT ON + CONSTRAINT</code> <em class="replaceable"><code> + constraint_name</code></em>. Inference will continue to work + correctly when the underlying index is replaced by another more + or less equivalent index in an overlapping way, for example when + using <code class="literal">CREATE UNIQUE INDEX ... CONCURRENTLY</code> + before dropping the index being replaced. + </p></div></div></div><div class="refsect1" id="id-1.9.3.152.7"><h2>Outputs</h2><p> + On successful completion, an <code class="command">INSERT</code> command returns a command + tag of the form +</p><pre class="screen"> +INSERT <em class="replaceable"><code>oid</code></em> <em class="replaceable"><code>count</code></em> +</pre><p> + The <em class="replaceable"><code>count</code></em> is the number of + rows inserted or updated. <em class="replaceable"><code>oid</code></em> is always 0 (it + used to be the <acronym class="acronym">OID</acronym> assigned to the inserted row if + <em class="replaceable"><code>count</code></em> was exactly one and the target table was + declared <code class="literal">WITH OIDS</code> and 0 otherwise, but creating a table + <code class="literal">WITH OIDS</code> is not supported anymore). + </p><p> + If the <code class="command">INSERT</code> command contains a <code class="literal">RETURNING</code> + clause, the result will be similar to that of a <code class="command">SELECT</code> + statement containing the columns and values defined in the + <code class="literal">RETURNING</code> list, computed over the row(s) inserted or + updated by the command. + </p></div><div class="refsect1" id="id-1.9.3.152.8"><h2>Notes</h2><p> + If the specified table is a partitioned table, each row is routed to + the appropriate partition and inserted into it. If the specified table + is a partition, an error will occur if one of the input rows violates + the partition constraint. + </p><p> + You may also wish to consider using <code class="command">MERGE</code>, since that + allows mixing <code class="command">INSERT</code>, <code class="command">UPDATE</code>, and + <code class="command">DELETE</code> within a single statement. + See <a class="xref" href="sql-merge.html" title="MERGE"><span class="refentrytitle">MERGE</span></a>. + </p></div><div class="refsect1" id="id-1.9.3.152.9"><h2>Examples</h2><p> + Insert a single row into table <code class="literal">films</code>: + +</p><pre class="programlisting"> +INSERT INTO films VALUES + ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'); +</pre><p> + </p><p> + In this example, the <code class="literal">len</code> column is + omitted and therefore it will have the default value: + +</p><pre class="programlisting"> +INSERT INTO films (code, title, did, date_prod, kind) + VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama'); +</pre><p> + </p><p> + This example uses the <code class="literal">DEFAULT</code> clause for + the date columns rather than specifying a value: + +</p><pre class="programlisting"> +INSERT INTO films VALUES + ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'); +INSERT INTO films (code, title, did, date_prod, kind) + VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama'); +</pre><p> + </p><p> + To insert a row consisting entirely of default values: + +</p><pre class="programlisting"> +INSERT INTO films DEFAULT VALUES; +</pre><p> + </p><p> + To insert multiple rows using the multirow <code class="command">VALUES</code> syntax: + +</p><pre class="programlisting"> +INSERT INTO films (code, title, did, date_prod, kind) VALUES + ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), + ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); +</pre><p> + </p><p> + This example inserts some rows into table + <code class="literal">films</code> from a table <code class="literal">tmp_films</code> + with the same column layout as <code class="literal">films</code>: + +</p><pre class="programlisting"> +INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07'; +</pre><p> + </p><p> + This example inserts into array columns: + +</p><pre class="programlisting"> +-- Create an empty 3x3 gameboard for noughts-and-crosses +INSERT INTO tictactoe (game, board[1:3][1:3]) + VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}'); +-- The subscripts in the above example aren't really needed +INSERT INTO tictactoe (game, board) + VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}'); +</pre><p> + </p><p> + Insert a single row into table <code class="literal">distributors</code>, returning + the sequence number generated by the <code class="literal">DEFAULT</code> clause: + +</p><pre class="programlisting"> +INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') + RETURNING did; +</pre><p> + </p><p> + Increment the sales count of the salesperson who manages the + account for Acme Corporation, and record the whole updated row + along with current time in a log table: +</p><pre class="programlisting"> +WITH upd AS ( + UPDATE employees SET sales_count = sales_count + 1 WHERE id = + (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation') + RETURNING * +) +INSERT INTO employees_log SELECT *, current_timestamp FROM upd; +</pre><p> + </p><p> + Insert or update new distributors as appropriate. Assumes a unique + index has been defined that constrains values appearing in the + <code class="literal">did</code> column. Note that the special + <code class="varname">excluded</code> table is used to reference values originally + proposed for insertion: +</p><pre class="programlisting"> +INSERT INTO distributors (did, dname) + VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') + ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname; +</pre><p> + </p><p> + Insert a distributor, or do nothing for rows proposed for insertion + when an existing, excluded row (a row with a matching constrained + column or columns after before row insert triggers fire) exists. + Example assumes a unique index has been defined that constrains + values appearing in the <code class="literal">did</code> column: +</p><pre class="programlisting"> +INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') + ON CONFLICT (did) DO NOTHING; +</pre><p> + </p><p> + Insert or update new distributors as appropriate. Example assumes + a unique index has been defined that constrains values appearing in + the <code class="literal">did</code> column. <code class="literal">WHERE</code> clause is + used to limit the rows actually updated (any existing row not + updated will still be locked, though): +</p><pre class="programlisting"> +-- Don't update existing distributors based in a certain ZIP code +INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution') + ON CONFLICT (did) DO UPDATE + SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')' + WHERE d.zipcode <> '21201'; + +-- Name a constraint directly in the statement (uses associated +-- index to arbitrate taking the DO NOTHING action) +INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design') + ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING; +</pre><p> + </p><p> + Insert new distributor if possible; otherwise + <code class="literal">DO NOTHING</code>. Example assumes a unique index has been + defined that constrains values appearing in the + <code class="literal">did</code> column on a subset of rows where the + <code class="literal">is_active</code> Boolean column evaluates to + <code class="literal">true</code>: +</p><pre class="programlisting"> +-- This statement could infer a partial unique index on "did" +-- with a predicate of "WHERE is_active", but it could also +-- just use a regular unique constraint on "did" +INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') + ON CONFLICT (did) WHERE is_active DO NOTHING; +</pre></div><div class="refsect1" id="id-1.9.3.152.10"><h2>Compatibility</h2><p> + <code class="command">INSERT</code> conforms to the SQL standard, except that + the <code class="literal">RETURNING</code> clause is a + <span class="productname">PostgreSQL</span> extension, as is the ability + to use <code class="literal">WITH</code> with <code class="command">INSERT</code>, and the ability to + specify an alternative action with <code class="literal">ON CONFLICT</code>. + Also, the case in + which a column name list is omitted, but not all the columns are + filled from the <code class="literal">VALUES</code> clause or <em class="replaceable"><code>query</code></em>, + is disallowed by the standard. If you prefer a more SQL standard + conforming statement than <code class="literal">ON CONFLICT</code>, see + <a class="xref" href="sql-merge.html" title="MERGE"><span class="refentrytitle">MERGE</span></a>. + </p><p> + The SQL standard specifies that <code class="literal">OVERRIDING SYSTEM VALUE</code> + can only be specified if an identity column that is generated always + exists. PostgreSQL allows the clause in any case and ignores it if it is + not applicable. + </p><p> + Possible limitations of the <em class="replaceable"><code>query</code></em> clause are documented under + <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>. + </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 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> LISTEN</td></tr></table></div></body></html>
\ No newline at end of file |