INSERT .. SET syntax

*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***************
*** 22,33 **** PostgreSQL documentation
   <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> [, ...] ) ]
!     { 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>
--- 22,42 ----
   <refsynopsisdiv>
  <synopsis>
  [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
! INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
!     {
!         [ column_list ] VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] |
!         [ column_list ] <replaceable class="PARAMETER">query</replaceable> |
!         DEFAULT VALUES |
!         SET <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
!     }
      [ 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">column_list</replaceable> is:</phrase>
! 
!     ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] )
! 
! <phrase>and <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>
***************
*** 53,65 **** INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
    </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</> column
!    names, if there are only <replaceable>N</> columns supplied by the
!    <literal>VALUES</> clause or <replaceable>query</>.  The values
!    supplied by the <literal>VALUES</> clause or <replaceable>query</> are
!    associated with the explicit or implicit column list left-to-right.
    </para>
  
    <para>
--- 62,87 ----
    </para>
  
    <para>
!    The target column names in a <replaceable>column_list</> can be
!    listed in any order.  If no <replaceable>column_list</> is given at
!    all (and the <literal>SET</> syntax is not used), the default is all
!    the columns of the table in their declared order; or the first
!    <replaceable>N</> column names, if there are only <replaceable>N</>
!    columns supplied by the <literal>VALUES</> clause or
!    <replaceable>query</>.  The values supplied by the <literal>VALUES</>
!    clause or <replaceable>query</> are associated with the explicit or
!    implicit column list left-to-right.
!   </para>
! 
!   <para>
!     Instead of a <replaceable>column_list</> and a <literal>VALUES</>
!     clause, a <literal>SET</> clause similar to that of an
!     <command>UPDATE</command> can be used instead.  The advantage of the
!     <literal>SET</> clause is that instead of matching the elements in
!     the two lists by ordinal position, the column name and the
!     expression to assign to that column are visually next to each other.
!     This can make long column assignment lists significantly more
!     readable.
    </para>
  
    <para>
***************
*** 690,702 **** INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    <para>
     <command>INSERT</command> conforms to the SQL standard, except that
     the <literal>RETURNING</> clause is a
!    <productname>PostgreSQL</productname> extension, as is the ability
!    to use <literal>WITH</> with <command>INSERT</>, and the ability to
!    specify an alternative action with <literal>ON CONFLICT</>.
!    Also, the case in
!    which a column name list is omitted, but not all the columns are
!    filled from the <literal>VALUES</> clause or <replaceable>query</>,
!    is disallowed by the standard.
    </para>
  
    <para>
--- 712,724 ----
    <para>
     <command>INSERT</command> conforms to the SQL standard, except that
     the <literal>RETURNING</> clause is a
!    <productname>PostgreSQL</productname> extension, as is the
!    <literal>SET</> clause when used instead of a VALUES clause, the
!    ability to use <literal>WITH</> with <command>INSERT</>, and the
!    ability to specify an alternative action with <literal>ON
!    CONFLICT</>.  Also, the case in which a column name list is omitted,
!    but not all the columns are filled from the <literal>VALUES</> clause
!    or <replaceable>query</>, is disallowed by the standard.
    </para>
  
    <para>
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***************
*** 467,474 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
  						stmt->onConflictClause->action == ONCONFLICT_UPDATE);
  
  	/*
! 	 * We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
! 	 * VALUES list, or general SELECT input.  We special-case VALUES, both for
  	 * efficiency and so we can handle DEFAULT specifications.
  	 *
  	 * The grammar allows attaching ORDER BY, LIMIT, FOR UPDATE, or WITH to a
--- 467,475 ----
  						stmt->onConflictClause->action == ONCONFLICT_UPDATE);
  
  	/*
! 	 * We have four cases to deal with: DEFAULT VALUES (selectStmt == NULL and
! 	 * cols == NIL), SET syntax (selectStmt == NULL but cols != NIL), VALUES
! 	 * list, or general SELECT input.  We special-case VALUES, both for
  	 * efficiency and so we can handle DEFAULT specifications.
  	 *
  	 * The grammar allows attaching ORDER BY, LIMIT, FOR UPDATE, or WITH to a
***************
*** 523,529 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
  	/*
  	 * Determine which variant of INSERT we have.
  	 */
! 	if (selectStmt == NULL)
  	{
  		/*
  		 * We have INSERT ... DEFAULT VALUES.  We can handle this case by
--- 524,530 ----
  	/*
  	 * Determine which variant of INSERT we have.
  	 */
! 	if (selectStmt == NULL && stmt->cols == NIL)
  	{
  		/*
  		 * We have INSERT ... DEFAULT VALUES.  We can handle this case by
***************
*** 532,537 **** transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
--- 533,557 ----
  		 */
  		exprList = NIL;
  	}
+ 	else if (selectStmt == NULL)
+ 	{
+ 		/*
+ 		 * INSERT INTO ... SET syntax.
+ 		 */
+ 		Assert(stmt->cols != NIL);
+ 
+ 		stmt->cols = transformUpdateTargetList(pstate, stmt->cols);
+ 
+ 		exprList = NIL;
+ 		foreach(lc, stmt->cols)
+ 		{
+ 			TargetEntry *rt = (TargetEntry *) lfirst(lc);
+ 
+ 			Assert(IsA(rt, TargetEntry));
+ 
+ 			exprList = lappend(exprList, rt->expr);
+ 		}
+ 	}
  	else if (isGeneralSelect)
  	{
  		/*
***************
*** 2179,2185 **** transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
  
  /*
   * transformUpdateTargetList -
!  *	handle SET clause in UPDATE/INSERT ... ON CONFLICT UPDATE
   */
  static List *
  transformUpdateTargetList(ParseState *pstate, List *origTlist)
--- 2199,2206 ----
  
  /*
   * transformUpdateTargetList -
!  *	handle SET clause in UPDATE / INSERT ... ON CONFLICT UPDATE / INSERT INTO
!  *	... SET
   */
  static List *
  transformUpdateTargetList(ParseState *pstate, List *origTlist)
*** a/src/backend/parser/gram.y
--- b/src/backend/parser/gram.y
***************
*** 362,368 **** static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
  				qualified_name_list any_name any_name_list type_name_list
  				any_operator expr_list attrs
  				target_list opt_target_list insert_column_list set_target_list
! 				set_clause_list set_clause multiple_set_clause
  				ctext_expr_list ctext_row def_list operator_def_list indirection opt_indirection
  				reloption_list group_clause TriggerFuncArgs select_limit
  				opt_select_limit opclass_item_list opclass_drop_list
--- 362,368 ----
  				qualified_name_list any_name any_name_list type_name_list
  				any_operator expr_list attrs
  				target_list opt_target_list insert_column_list set_target_list
! 				insert_set_clause_list set_clause_list set_clause multiple_set_clause
  				ctext_expr_list ctext_row def_list operator_def_list indirection opt_indirection
  				reloption_list group_clause TriggerFuncArgs select_limit
  				opt_select_limit opclass_item_list opclass_drop_list
***************
*** 9661,9666 **** insert_rest:
--- 9661,9672 ----
  					$$->cols = $2;
  					$$->selectStmt = $4;
  				}
+ 			| SET insert_set_clause_list
+ 				{
+ 					$$ = makeNode(InsertStmt);
+ 					$$->cols = $2;
+ 					$$->selectStmt = NULL;
+ 				}
  			| DEFAULT VALUES
  				{
  					$$ = makeNode(InsertStmt);
***************
*** 9687,9692 **** insert_column_item:
--- 9693,9709 ----
  				}
  		;
  
+ /*
+  * This is different from set_clause_list used in UPDATE because the SelectStmt
+  * syntax already does everything you might want to do in an in INSERT.
+  */
+ insert_set_clause_list:
+ 			single_set_clause
+ 				{ $$ = list_make1($1); }
+ 			| insert_set_clause_list ',' single_set_clause
+ 				{ $$ = lappend($1,$3); }
+ 		;
+ 
  opt_on_conflict:
  			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
  				{
*** a/src/include/nodes/parsenodes.h
--- b/src/include/nodes/parsenodes.h
***************
*** 406,414 **** typedef struct A_ArrayExpr
   * 'AS ColumnLabel' clause, or NULL if there was none, and 'val' is the
   * value expression itself.  The 'indirection' field is not used.
   *
!  * INSERT uses ResTarget in its target-column-names list.  Here, 'name' is
!  * the name of the destination column, 'indirection' stores any subscripts
!  * attached to the destination, and 'val' is not used.
   *
   * In an UPDATE target list, 'name' is the name of the destination column,
   * 'indirection' stores any subscripts attached to the destination, and
--- 406,416 ----
   * 'AS ColumnLabel' clause, or NULL if there was none, and 'val' is the
   * value expression itself.  The 'indirection' field is not used.
   *
!  * INSERT uses ResTarget in its "cols" list.  Here, 'name' is the name of the
!  * destination column, 'indirection' stores any subscripts attached to the
!  * destination.  'val' is only used if "cols" is from an INSERT ... SET syntax
!  * statement; otherwise "cols" only represents the column name list, and thus
!  * 'val' is unused.
   *
   * In an UPDATE target list, 'name' is the name of the destination column,
   * 'indirection' stores any subscripts attached to the destination, and
*** a/src/test/regress/expected/insert.out
--- b/src/test/regress/expected/insert.out
***************
*** 80,85 **** select col1, col2, char_length(col3) from inserttest;
--- 80,177 ----
     30 |   50 |       10000
  (8 rows)
  
+ --
+ -- insert with SET syntax
+ --
+ truncate inserttest;
+ create type inserttestcomplextype as (f1 int, f2 int);
+ alter table inserttest add column col4 int[];
+ alter table inserttest add column col5 inserttestcomplextype;
+ -- these all fail
+ insert into inserttest set
+     colnonexistent = 1;
+ ERROR:  column "colnonexistent" of relation "inserttest" does not exist
+ LINE 2:     colnonexistent = 1;
+             ^
+ insert into inserttest set
+     col1 = col1;
+ ERROR:  column "col1" does not exist
+ LINE 2:     col1 = col1;
+                    ^
+ HINT:  There is a column named "col1" in table "inserttest", but it cannot be referenced from this part of the query.
+ insert into inserttest set
+     col4[1] = 1,
+     col4 = '{}';
+ ERROR:  column "col4" specified more than once
+ LINE 3:     col4 = '{}';
+             ^
+ insert into inserttest set
+     col5.nonexistent = 1;
+ ERROR:  cannot assign to field "nonexistent" of column "col5" because there is no such column in data type inserttestcomplextype
+ LINE 2:     col5.nonexistent = 1;
+             ^
+ insert into inserttest set
+     col5.f1 = 1,
+     col5 = '()';
+ ERROR:  column "col5" specified more than once
+ LINE 3:     col5 = '()';
+             ^
+ insert into inserttest set
+     col1 = 1,
+     col1 = 1;
+ ERROR:  column "col1" specified more than once
+ LINE 3:     col1 = 1;
+             ^
+ select * from inserttest;
+  col1 | col2 | col3 | col4 | col5 
+ ------+------+------+------+------
+ (0 rows)
+ 
+ truncate table inserttest;
+ -- defaults
+ insert into inserttest set
+     col1 = default,
+     col2 = default,
+     col3 = default;
+ ERROR:  null value in column "col2" violates not-null constraint
+ DETAIL:  Failing row contains (null, null, testing, null, null).
+ insert into inserttest set
+     col2 = 3,
+     col3 = default;
+ insert into inserttest set
+     col1 = default,
+     col2 = 5,
+     col3 = default;
+ select * from inserttest;
+  col1 | col2 |  col3   | col4 | col5 
+ ------+------+---------+------+------
+       |    3 | testing |      | 
+       |    5 | testing |      | 
+ (2 rows)
+ 
+ truncate table inserttest;
+ -- more complex cases
+ insert into inserttest set
+     col2 = (select count(*))::int;
+ insert into inserttest set
+     col2 = 1,
+     col4[1] = 1,
+     -- weird, but accepted
+     col4[1] = 2;
+ insert into inserttest set
+     col2 = 1,
+     col5.f1 = 1,
+     -- weird, but accepted
+     col5.f1 = 2;
+ select * from inserttest;
+  col1 | col2 |  col3   | col4 | col5 
+ ------+------+---------+------+------
+       |    1 | testing |      | 
+       |    1 | testing | {2}  | 
+       |    1 | testing |      | (2,)
+ (3 rows)
+ 
+ truncate table inserttest;
  drop table inserttest;
  --
  -- check indirection (field/array assignment), cf bug #14265
***************
*** 160,162 **** Rules:
--- 252,255 ----
  drop table inserttest2;
  drop table inserttest;
  drop type insert_test_type;
+ drop type inserttestcomplextype;
*** a/src/test/regress/sql/insert.sql
--- b/src/test/regress/sql/insert.sql
***************
*** 35,40 **** insert into inserttest values(30, 50, repeat('x', 10000));
--- 35,102 ----
  
  select col1, col2, char_length(col3) from inserttest;
  
+ --
+ -- insert with SET syntax
+ --
+ truncate inserttest;
+ 
+ create type inserttestcomplextype as (f1 int, f2 int);
+ alter table inserttest add column col4 int[];
+ alter table inserttest add column col5 inserttestcomplextype;
+ 
+ -- these all fail
+ insert into inserttest set
+     colnonexistent = 1;
+ insert into inserttest set
+     col1 = col1;
+ insert into inserttest set
+     col4[1] = 1,
+     col4 = '{}';
+ insert into inserttest set
+     col5.nonexistent = 1;
+ insert into inserttest set
+     col5.f1 = 1,
+     col5 = '()';
+ insert into inserttest set
+     col1 = 1,
+     col1 = 1;
+ 
+ select * from inserttest;
+ truncate table inserttest;
+ 
+ -- defaults
+ insert into inserttest set
+     col1 = default,
+     col2 = default,
+     col3 = default;
+ insert into inserttest set
+     col2 = 3,
+     col3 = default;
+ insert into inserttest set
+     col1 = default,
+     col2 = 5,
+     col3 = default;
+ 
+ select * from inserttest;
+ truncate table inserttest;
+ 
+ -- more complex cases
+ insert into inserttest set
+     col2 = (select count(*))::int;
+ insert into inserttest set
+     col2 = 1,
+     col4[1] = 1,
+     -- weird, but accepted
+     col4[1] = 2;
+ insert into inserttest set
+     col2 = 1,
+     col5.f1 = 1,
+     -- weird, but accepted
+     col5.f1 = 2;
+ 
+ select * from inserttest;
+ truncate table inserttest;
+ 
  drop table inserttest;
  
  --
***************
*** 84,86 **** create rule irule3 as on insert to inserttest2 do also
--- 146,149 ----
  drop table inserttest2;
  drop table inserttest;
  drop type insert_test_type;
+ drop type inserttestcomplextype;