| <!-- |
| 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> |
| <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 rows 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 rows 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> |
| </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. |
| </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> |
