diff options
Diffstat (limited to 'doc/src/sgml/ref/insert.sgml')
| -rw-r--r-- | doc/src/sgml/ref/insert.sgml | 792 |
1 files changed, 792 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml new file mode 100644 index 0000000..7cea703 --- /dev/null +++ b/doc/src/sgml/ref/insert.sgml @@ -0,0 +1,792 @@ +<!-- +doc/src/sgml/ref/insert.sgml +PostgreSQL documentation +--> + +<refentry id="sql-insert"> + <indexterm zone="sql-insert"> + <primary>INSERT</primary> + </indexterm> + + <refmeta> + <refentrytitle>INSERT</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>INSERT</refname> + <refpurpose>create new rows in a table</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ] +INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] + [ OVERRIDING { SYSTEM | USER } VALUE ] + { DEFAULT VALUES | VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="parameter">query</replaceable> } + [ ON CONFLICT [ <replaceable class="parameter">conflict_target</replaceable> ] <replaceable class="parameter">conflict_action</replaceable> ] + [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ] + +<phrase>where <replaceable class="parameter">conflict_target</replaceable> can be one of:</phrase> + + ( { <replaceable class="parameter">index_column_name</replaceable> | ( <replaceable class="parameter">index_expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) [ WHERE <replaceable class="parameter">index_predicate</replaceable> ] + ON CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> + +<phrase>and <replaceable class="parameter">conflict_action</replaceable> is one of:</phrase> + + DO NOTHING + DO UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } | + ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | + ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> ) + } [, ...] + [ WHERE <replaceable class="parameter">condition</replaceable> ] +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>INSERT</command> inserts new rows into a table. + One can insert one or more rows specified by value expressions, + or zero or more rows resulting from a query. + </para> + + <para> + The target column names can be listed in any order. If no list of + column names is given at all, the default is all the columns of the + table in their declared order; or the first <replaceable>N</replaceable> column + names, if there are only <replaceable>N</replaceable> columns supplied by the + <literal>VALUES</literal> clause or <replaceable>query</replaceable>. The values + supplied by the <literal>VALUES</literal> clause or <replaceable>query</replaceable> are + associated with the explicit or implicit column list left-to-right. + </para> + + <para> + Each column not present in the explicit or implicit column list will be + filled with a default value, either its declared default value + or null if there is none. + </para> + + <para> + If the expression for any column is not of the correct data type, + automatic type conversion will be attempted. + </para> + + <para> + <command>INSERT</command> 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 <xref linkend="index-unique-checks"/>. + <literal>ON CONFLICT</literal> can be used to specify an alternative + action to raising a unique constraint or exclusion constraint + violation error. (See <xref linkend="sql-on-conflict"/> below.) + </para> + + <para> + The optional <literal>RETURNING</literal> clause causes <command>INSERT</command> + to compute and return value(s) based on each row actually inserted + (or updated, if an <literal>ON CONFLICT DO UPDATE</literal> 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 <literal>RETURNING</literal> list is identical to that of the output + list of <command>SELECT</command>. Only rows that were successfully + inserted or updated will be returned. For example, if a row was + locked but not updated because an <literal>ON CONFLICT DO UPDATE + ... WHERE</literal> clause <replaceable + class="parameter">condition</replaceable> was not satisfied, the + row will not be returned. + </para> + + <para> + You must have <literal>INSERT</literal> privilege on a table in + order to insert into it. If <literal>ON CONFLICT DO UPDATE</literal> is + present, <literal>UPDATE</literal> privilege on the table is also + required. + </para> + + <para> + If a column list is specified, you only need + <literal>INSERT</literal> privilege on the listed columns. + Similarly, when <literal>ON CONFLICT DO UPDATE</literal> is specified, you + only need <literal>UPDATE</literal> privilege on the column(s) that are + listed to be updated. However, <literal>ON CONFLICT DO UPDATE</literal> + also requires <literal>SELECT</literal> privilege on any column whose + values are read in the <literal>ON CONFLICT DO UPDATE</literal> + expressions or <replaceable>condition</replaceable>. + </para> + + <para> + Use of the <literal>RETURNING</literal> clause requires <literal>SELECT</literal> + privilege on all columns mentioned in <literal>RETURNING</literal>. + If you use the <replaceable + class="parameter">query</replaceable> clause to insert rows from a + query, you of course need to have <literal>SELECT</literal> privilege on + any table or column used in the query. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <refsect2> + <title>Inserting</title> + + <para> + This section covers parameters that may be used when only + inserting new rows. Parameters <emphasis>exclusively</emphasis> + used with the <literal>ON CONFLICT</literal> clause are described + separately. + </para> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">with_query</replaceable></term> + <listitem> + <para> + The <literal>WITH</literal> clause allows you to specify one or more + subqueries that can be referenced by name in the <command>INSERT</command> + query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/> + for details. + </para> + <para> + It is possible for the <replaceable class="parameter">query</replaceable> + (<command>SELECT</command> statement) + to also contain a <literal>WITH</literal> clause. In such a case both + sets of <replaceable>with_query</replaceable> can be referenced within + the <replaceable class="parameter">query</replaceable>, but the + second one takes precedence since it is more closely nested. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">table_name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of an existing table. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">alias</replaceable></term> + <listitem> + <para> + A substitute name for <replaceable + class="parameter">table_name</replaceable>. When an alias is + provided, it completely hides the actual name of the table. + This is particularly useful when <literal>ON CONFLICT DO UPDATE</literal> + targets a table named <varname>excluded</varname>, since that will otherwise + be taken as the name of the special table representing the row proposed + for insertion. + </para> + </listitem> + </varlistentry> + + + <varlistentry> + <term><replaceable class="parameter">column_name</replaceable></term> + <listitem> + <para> + The name of a column in the table named by <replaceable + class="parameter">table_name</replaceable>. 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 <literal>ON CONFLICT DO UPDATE</literal>, do not include + the table's name in the specification of a target column. For + example, <literal>INSERT INTO table_name ... ON CONFLICT DO UPDATE + SET table_name.col = 1</literal> is invalid (this follows the general + behavior for <command>UPDATE</command>). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>OVERRIDING SYSTEM VALUE</literal></term> + <listitem> + <para> + If this clause is specified, then any values supplied for identity + columns will override the default sequence-generated values. + </para> + + <para> + For an identity column defined as <literal>GENERATED ALWAYS</literal>, + it is an error to insert an explicit value (other than + <literal>DEFAULT</literal>) without specifying either + <literal>OVERRIDING SYSTEM VALUE</literal> or <literal>OVERRIDING USER + VALUE</literal>. (For an identity column defined as + <literal>GENERATED BY DEFAULT</literal>, <literal>OVERRIDING SYSTEM + VALUE</literal> is the normal behavior and specifying it does nothing, + but <productname>PostgreSQL</productname> allows it as an extension.) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>OVERRIDING USER VALUE</literal></term> + <listitem> + <para> + If this clause is specified, then any values supplied for identity + columns are ignored and the default sequence-generated values are + applied. + </para> + + <para> + This clause is useful for example when copying values between tables. + Writing <literal>INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM + tbl1</literal> will copy from <literal>tbl1</literal> all columns that + are not identity columns in <literal>tbl2</literal> while values for + the identity columns in <literal>tbl2</literal> will be generated by + the sequences associated with <literal>tbl2</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFAULT VALUES</literal></term> + <listitem> + <para> + All columns will be filled with their default values, as if + <literal>DEFAULT</literal> were explicitly specified for each column. + (An <literal>OVERRIDING</literal> clause is not permitted in this + form.) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">expression</replaceable></term> + <listitem> + <para> + An expression or value to assign to the corresponding column. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFAULT</literal></term> + <listitem> + <para> + 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. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">query</replaceable></term> + <listitem> + <para> + A query (<command>SELECT</command> statement) that supplies the + rows to be inserted. Refer to the + <xref linkend="sql-select"/> + statement for a description of the syntax. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">output_expression</replaceable></term> + <listitem> + <para> + An expression to be computed and returned by the + <command>INSERT</command> command after each row is inserted or + updated. The expression can use any column names of the table + named by <replaceable + class="parameter">table_name</replaceable>. Write + <literal>*</literal> to return all columns of the inserted or updated + row(s). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">output_name</replaceable></term> + <listitem> + <para> + A name to use for a returned column. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect2> + + <refsect2 id="sql-on-conflict" xreflabel="ON CONFLICT Clause"> + <title><literal>ON CONFLICT</literal> Clause</title> + <indexterm zone="sql-insert"> + <primary>UPSERT</primary> + </indexterm> + <indexterm zone="sql-insert"> + <primary>ON CONFLICT</primary> + </indexterm> + <para> + The optional <literal>ON CONFLICT</literal> 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 + <emphasis>arbiter</emphasis> constraint or index specified by + <parameter>conflict_target</parameter> is violated, the + alternative <parameter>conflict_action</parameter> is taken. + <literal>ON CONFLICT DO NOTHING</literal> simply avoids inserting + a row as its alternative action. <literal>ON CONFLICT DO + UPDATE</literal> updates the existing row that conflicts with the + row proposed for insertion as its alternative action. + </para> + + <para> + <parameter>conflict_target</parameter> can perform + <emphasis>unique index inference</emphasis>. When performing + inference, it consists of one or more <replaceable + class="parameter">index_column_name</replaceable> columns and/or + <replaceable class="parameter">index_expression</replaceable> + expressions, and an optional <replaceable class="parameter">index_predicate</replaceable>. All <replaceable + class="parameter">table_name</replaceable> unique indexes that, + without regard to order, contain exactly the + <parameter>conflict_target</parameter>-specified + columns/expressions are inferred (chosen) as arbiter indexes. If + an <replaceable class="parameter">index_predicate</replaceable> 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 <literal>ON CONFLICT</literal>) if such an index + satisfying every other criteria is available. If an attempt at + inference is unsuccessful, an error is raised. + </para> + + <para> + <literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic + <command>INSERT</command> or <command>UPDATE</command> outcome; + provided there is no independent error, one of those two outcomes + is guaranteed, even under high concurrency. This is also known as + <firstterm>UPSERT</firstterm> — <quote>UPDATE or + INSERT</quote>. + </para> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">conflict_target</replaceable></term> + <listitem> + <para> + Specifies which conflicts <literal>ON CONFLICT</literal> takes + the alternative action on by choosing <firstterm>arbiter + indexes</firstterm>. Either performs <emphasis>unique index + inference</emphasis>, or names a constraint explicitly. For + <literal>ON CONFLICT DO NOTHING</literal>, it is optional to + specify a <parameter>conflict_target</parameter>; when + omitted, conflicts with all usable constraints (and unique + indexes) are handled. For <literal>ON CONFLICT DO + UPDATE</literal>, a <parameter>conflict_target</parameter> + <emphasis>must</emphasis> be provided. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">conflict_action</replaceable></term> + <listitem> + <para> + <parameter>conflict_action</parameter> specifies an + alternative <literal>ON CONFLICT</literal> action. It can be + either <literal>DO NOTHING</literal>, or a <literal>DO + UPDATE</literal> clause specifying the exact details of the + <literal>UPDATE</literal> action to be performed in case of a + conflict. The <literal>SET</literal> and + <literal>WHERE</literal> clauses in <literal>ON CONFLICT DO + UPDATE</literal> have access to the existing row using the + table's name (or an alias), and to the row proposed for insertion + using the special <varname>excluded</varname> table. + <literal>SELECT</literal> privilege is required on any column in the + target table where corresponding <varname>excluded</varname> + columns are read. + </para> + <para> + Note that the effects of all per-row <literal>BEFORE + INSERT</literal> triggers are reflected in + <varname>excluded</varname> values, since those effects may + have contributed to the row being excluded from insertion. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">index_column_name</replaceable></term> + <listitem> + <para> + The name of a <replaceable + class="parameter">table_name</replaceable> column. Used to + infer arbiter indexes. Follows <command>CREATE + INDEX</command> format. <literal>SELECT</literal> privilege on + <replaceable class="parameter">index_column_name</replaceable> + is required. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">index_expression</replaceable></term> + <listitem> + <para> + Similar to <replaceable + class="parameter">index_column_name</replaceable>, but used to + infer expressions on <replaceable + class="parameter">table_name</replaceable> columns appearing + within index definitions (not simple columns). Follows + <command>CREATE INDEX</command> format. <literal>SELECT</literal> + privilege on any column appearing within <replaceable + class="parameter">index_expression</replaceable> is required. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">collation</replaceable></term> + <listitem> + <para> + When specified, mandates that corresponding <replaceable + class="parameter">index_column_name</replaceable> or + <replaceable class="parameter">index_expression</replaceable> + 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 <command>CREATE INDEX</command> format. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">opclass</replaceable></term> + <listitem> + <para> + When specified, mandates that corresponding <replaceable + class="parameter">index_column_name</replaceable> or + <replaceable class="parameter">index_expression</replaceable> + use particular operator class in order to be matched during + inference. Typically this is omitted, as the + <emphasis>equality</emphasis> 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 <command>CREATE + INDEX</command> format. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">index_predicate</replaceable></term> + <listitem> + <para> + 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 <command>CREATE + INDEX</command> format. <literal>SELECT</literal> privilege on any + column appearing within <replaceable + class="parameter">index_predicate</replaceable> is required. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">constraint_name</replaceable></term> + <listitem> + <para> + Explicitly specifies an arbiter + <emphasis>constraint</emphasis> by name, rather than inferring + a constraint or index. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">condition</replaceable></term> + <listitem> + <para> + An expression that returns a value of type + <type>boolean</type>. Only rows for which this expression + returns <literal>true</literal> will be updated, although all + rows will be locked when the <literal>ON CONFLICT DO UPDATE</literal> + action is taken. Note that + <replaceable>condition</replaceable> is evaluated last, after + a conflict has been identified as a candidate to update. + </para> + </listitem> + </varlistentry> + </variablelist> + <para> + Note that exclusion constraints are not supported as arbiters with + <literal>ON CONFLICT DO UPDATE</literal>. In all cases, only + <literal>NOT DEFERRABLE</literal> constraints and unique indexes + are supported as arbiters. + </para> + + <para> + <command>INSERT</command> with an <literal>ON CONFLICT DO UPDATE</literal> + clause is a <quote>deterministic</quote> 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. + </para> + + <para> + Note that it is currently not supported for the + <literal>ON CONFLICT DO UPDATE</literal> clause of an + <command>INSERT</command> 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. + </para> + <tip> + <para> + It is often preferable to use unique index inference rather than + naming a constraint directly using <literal>ON CONFLICT ON + CONSTRAINT</literal> <replaceable class="parameter"> + constraint_name</replaceable>. 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 <literal>CREATE UNIQUE INDEX ... CONCURRENTLY</literal> + before dropping the index being replaced. + </para> + </tip> + + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + + <para> + On successful completion, an <command>INSERT</command> command returns a command + tag of the form +<screen> +INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable> +</screen> + The <replaceable class="parameter">count</replaceable> is the number of + rows inserted or updated. <replaceable>oid</replaceable> is always 0 (it + used to be the <acronym>OID</acronym> assigned to the inserted row if + <replaceable>count</replaceable> was exactly one and the target table was + declared <literal>WITH OIDS</literal> and 0 otherwise, but creating a table + <literal>WITH OIDS</literal> is not supported anymore). + </para> + + <para> + If the <command>INSERT</command> command contains a <literal>RETURNING</literal> + clause, the result will be similar to that of a <command>SELECT</command> + statement containing the columns and values defined in the + <literal>RETURNING</literal> list, computed over the row(s) inserted or + updated by the command. + </para> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + 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. + </para> + + <para> + You may also wish to consider using <command>MERGE</command>, since that + allows mixing <command>INSERT</command>, <command>UPDATE</command>, and + <command>DELETE</command> within a single statement. + See <xref linkend="sql-merge"/>. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + Insert a single row into table <literal>films</literal>: + +<programlisting> +INSERT INTO films VALUES + ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'); +</programlisting> + </para> + + <para> + In this example, the <literal>len</literal> column is + omitted and therefore it will have the default value: + +<programlisting> +INSERT INTO films (code, title, did, date_prod, kind) + VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama'); +</programlisting> + </para> + + <para> + This example uses the <literal>DEFAULT</literal> clause for + the date columns rather than specifying a value: + +<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'); +</programlisting> + </para> + + <para> + To insert a row consisting entirely of default values: + +<programlisting> +INSERT INTO films DEFAULT VALUES; +</programlisting> + </para> + + <para> + To insert multiple rows using the multirow <command>VALUES</command> syntax: + +<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'); +</programlisting> + </para> + + <para> + This example inserts some rows into table + <literal>films</literal> from a table <literal>tmp_films</literal> + with the same column layout as <literal>films</literal>: + +<programlisting> +INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07'; +</programlisting> + </para> + + <para> + This example inserts into array columns: + +<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," "}}'); +</programlisting> + </para> + + <para> + Insert a single row into table <literal>distributors</literal>, returning + the sequence number generated by the <literal>DEFAULT</literal> clause: + +<programlisting> +INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') + RETURNING did; +</programlisting> + </para> + + <para> + 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: +<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; +</programlisting> + </para> + <para> + Insert or update new distributors as appropriate. Assumes a unique + index has been defined that constrains values appearing in the + <literal>did</literal> column. Note that the special + <varname>excluded</varname> table is used to reference values originally + proposed for insertion: +<programlisting> +INSERT INTO distributors (did, dname) + VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') + ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname; +</programlisting> + </para> + <para> + 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 <literal>did</literal> column: +<programlisting> +INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') + ON CONFLICT (did) DO NOTHING; +</programlisting> + </para> + <para> + Insert or update new distributors as appropriate. Example assumes + a unique index has been defined that constrains values appearing in + the <literal>did</literal> column. <literal>WHERE</literal> clause is + used to limit the rows actually updated (any existing row not + updated will still be locked, though): +<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; +</programlisting> + </para> + <para> + Insert new distributor if possible; otherwise + <literal>DO NOTHING</literal>. Example assumes a unique index has been + defined that constrains values appearing in the + <literal>did</literal> column on a subset of rows where the + <literal>is_active</literal> Boolean column evaluates to + <literal>true</literal>: +<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; +</programlisting></para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + <command>INSERT</command> conforms to the SQL standard, except that + the <literal>RETURNING</literal> clause is a + <productname>PostgreSQL</productname> extension, as is the ability + to use <literal>WITH</literal> with <command>INSERT</command>, and the ability to + specify an alternative action with <literal>ON CONFLICT</literal>. + Also, the case in + which a column name list is omitted, but not all the columns are + filled from the <literal>VALUES</literal> clause or <replaceable>query</replaceable>, + is disallowed by the standard. If you prefer a more SQL standard + conforming statement than <literal>ON CONFLICT</literal>, see + <xref linkend="sql-merge"/>. + </para> + + <para> + The SQL standard specifies that <literal>OVERRIDING SYSTEM VALUE</literal> + 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. + </para> + + <para> + Possible limitations of the <replaceable + class="parameter">query</replaceable> clause are documented under + <xref linkend="sql-select"/>. + </para> + </refsect1> +</refentry> |