Home • Explore • Blog • CI
☰



postgresql
6th chunk of `doc/src/sgml/ref/create_view.sgml`
a438d1ac794dc0c3b9c535d7af9100f88a515dd6082120a50000000100000fa8
 it satisfies all of the following conditions:

    <itemizedlist>
     <listitem>
      <para>
       The view must have exactly one entry in its <literal>FROM</literal> list,
       which must be a table or another updatable view.
      </para>
     </listitem>

     <listitem>
      <para>
       The view definition must not contain <literal>WITH</literal>,
       <literal>DISTINCT</literal>, <literal>GROUP BY</literal>, <literal>HAVING</literal>,
       <literal>LIMIT</literal>, or <literal>OFFSET</literal> clauses at the top level.
      </para>
     </listitem>

     <listitem>
      <para>
       The view definition must not contain set operations (<literal>UNION</literal>,
       <literal>INTERSECT</literal> or <literal>EXCEPT</literal>) at the top level.
      </para>
     </listitem>

     <listitem>
      <para>
       The view's select list must not contain any aggregates, window functions
       or set-returning functions.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    An automatically updatable view may contain a mix of updatable and
    non-updatable columns.  A column is updatable if it is a simple reference
    to an updatable column of the underlying base relation; otherwise the
    column is read-only, and an error will be raised if an
    <command>INSERT</command>, <command>UPDATE</command>, or
    <command>MERGE</command> statement attempts to assign a value to it.
   </para>

   <para>
    If the view is automatically updatable the system will convert any
    <command>INSERT</command>, <command>UPDATE</command>,
    <command>DELETE</command>, or <command>MERGE</command> statement
    on the view into the corresponding statement on the underlying base
    relation.  <command>INSERT</command> statements that have an <literal>ON
    CONFLICT UPDATE</literal> clause are fully supported.
   </para>

   <para>
    If an automatically updatable view contains a <literal>WHERE</literal>
    condition, the condition restricts which rows of the base relation are
    available to be modified by <command>UPDATE</command>,
    <command>DELETE</command>, and <command>MERGE</command>
    statements on the view.  However, an <command>UPDATE</command> or
    <command>MERGE</command> is allowed to
    change a row so that it no longer satisfies the <literal>WHERE</literal>
    condition, and thus is no longer visible through the view.  Similarly,
    an <command>INSERT</command> or <command>MERGE</command> command can
    potentially insert base-relation rows
    that do not satisfy the <literal>WHERE</literal> condition and thus are not
    visible through the view (<literal>ON CONFLICT UPDATE</literal> may
    similarly affect an existing row not visible through the view).
    The <literal>CHECK OPTION</literal> may be used to prevent
    <command>INSERT</command>, <command>UPDATE</command>, and
    <command>MERGE</command> commands from creating such rows that are not
    visible through the view.
   </para>

   <para>
    If an automatically updatable view is marked with the
    <literal>security_barrier</literal> property then all the view's <literal>WHERE</literal>
    conditions (and any conditions using operators which are marked as <literal>LEAKPROOF</literal>)
    will always be evaluated before any conditions that a user of the view has
    added.   See <xref linkend="rules-privileges"/> for full details.  Note that,
    due to this, rows which are not ultimately returned (because they do not
    pass the user's <literal>WHERE</literal> conditions) may still end up being locked.
    <command>EXPLAIN</command> can be used to see which conditions are
    applied at the relation level (and therefore do not lock rows) and which are
    not.
   </para>

   <para>
    A more complex view that does not satisfy all these conditions is
    read-only by default: the system will not allow an <command>INSERT</command>,
    <command>UPDATE</command>, <command>DELETE</command>, or <command>MERGE</command>
Title: Conditions for Updatable Views, Column Updatability, and Interaction with WHERE and CHECK OPTION Clauses
Summary
This section details the conditions that make a view automatically updatable (single FROM entry, no WITH, DISTINCT, GROUP BY, HAVING, LIMIT, OFFSET, set operations, aggregates, window functions, or set-returning functions). It explains the difference between updatable and read-only columns in a view. It covers how INSERT, UPDATE, DELETE, and MERGE statements are converted to the base relation. The section further explains how the WHERE clause in a view restricts modifiable rows, but UPDATE and INSERT can still introduce rows not visible through the view unless CHECK OPTION is used. Finally, it touches upon security_barrier property that ensures WHERE clauses are evaluated before user-added conditions, and that more complex views are read-only by default.