Home • Explore • Blog • CI
☰



postgresql
3rd chunk of `doc/src/sgml/ref/insert.sgml`
6a40573430fcc8bf8008ed3a9f3e3f6e4f8d45f39764227f0000000100000fa7
 <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
Title: INSERT Parameters: WITH Clause, Table and Column Names, and OVERRIDING SYSTEM VALUE
Summary
This section describes the parameters used with the INSERT command, focusing on the WITH clause for subqueries, specifying the target table name and optional alias, and defining the columns to be inserted into. It also covers the OVERRIDING SYSTEM VALUE clause, which allows explicit values to be inserted into identity columns, overriding the default sequence-generated values, and clarifies the behavior when using GENERATED ALWAYS identity columns.