[PATCH] Implement INSERT SET syntax

From 25fad5800d1f0054effa49e86c081306d9bee7fa Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth@internetnz.net.nz>
Date: Fri, 16 Aug 2019 11:56:42 +1200
Subject: [PATCH] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values may also be sourced from a CTE using a FROM clause:

WITH x AS (
  SELECT 'foo' AS c1, 'bar' AS c2, 'baz' AS c3
)
INSERT INTO t SET c1 = x.c1, c2 = x.c2, c3 = x.c3 FROM x;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 24 ++++++++++-
 src/backend/parser/gram.y                     | 40 ++++++++++++++++++-
 src/backend/parser/parse_expr.c               |  8 +++-
 src/test/regress/expected/insert.out          | 16 ++++++++
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 21 ++++++++++
 src/test/regress/sql/insert.sql               | 12 ++++++
 src/test/regress/sql/insert_conflict.sql      |  3 ++
 src/test/regress/sql/with.sql                 | 10 +++++
 9 files changed, 132 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index f995a7637f..341616d8b5 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -24,7 +24,12 @@ PostgreSQL documentation
 [ 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> }
+    { DEFAULT VALUES | VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) [, ...] |
+      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> )
+      } [, ...] [ FROM <replaceable class="parameter">from_list</replaceable> ] } |
+      <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> ] [, ...] ]
 
@@ -254,6 +259,16 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_list</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -675,6 +690,13 @@ WITH upd AS (
     RETURNING *
 )
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
+</programlisting>
+  </para>
+  <para>
+   Insert a single row into table <literal>distributors</literal> using a
+<literal>SET</literal> clause to specify the columns and values:
+<programlisting>
+INSERT INTO distributors SET did = 4, dname = 'Hammers Unlimited, LLC';
 </programlisting>
   </para>
   <para>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c97bb367f8..3015a2925a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -463,7 +463,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 
@@ -10892,6 +10892,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -10923,6 +10932,35 @@ insert_column_item:
 				}
 		;
 
+insert_set_clause:
+		SET set_clause_list from_clause
+			{
+				SelectStmt *n = makeNode(SelectStmt);
+
+				if ($3 != NULL)
+				{
+					n->targetList = $2;
+					n->fromClause = $3;
+				}
+				else
+				{
+					List *values = NIL;
+					ListCell *col_cell;
+
+					foreach(col_cell, $2)
+					{
+						ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+
+						values = lappend(values, res_col->val);
+					}
+					n->valuesLists = list_make1(values);
+				}
+				$$ = makeNode(InsertStmt);
+				$$->cols = $2;
+				$$->selectStmt = (Node *) n;
+			}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 76f3dd7076..63ef879447 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1566,8 +1566,12 @@ transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref)
 	Query	   *qtree;
 	TargetEntry *tle;
 
-	/* We should only see this in first-stage processing of UPDATE tlists */
-	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE);
+	/*
+	 * We should only see this in first-stage processing of UPDATE tlists
+	 * or of an INSERT SET tlist.
+	 */
+	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE ||
+		   pstate->p_expr_kind == EXPR_KIND_VALUES_SINGLE);
 
 	/* We only need to transform the source if this is the first column */
 	if (maref->colno == 1)
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 75e25cdf48..c15260ea76 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -80,6 +80,22 @@ select col1, col2, char_length(col3) from inserttest;
    30 |   50 |       10000
 (8 rows)
 
+drop table inserttest;
+--
+-- SET test
+--
+create table inserttest (col1 int4, col2 text default 'bar');
+insert into inserttest set col1 = 1, col2 = 'foo';
+insert into inserttest set col1 = 2, col2 = DEFAULT;
+insert into inserttest set (col1, col2) = (3, 'baz');
+select * from inserttest;
+ col1 | col2 
+------+------
+    1 | foo
+    2 | bar
+    3 | baz
+(3 rows)
+
 drop table inserttest;
 --
 -- check indirection (field/array assignment), cf bug #14265
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e..4d6538b1fb 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 2a2085556b..b92f9f8a13 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -952,6 +952,27 @@ SELECT * FROM y;
  10
 (10 rows)
 
+DROP TABLE y;
+CREATE TEMPORARY TABLE y (a INTEGER);
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 23885f638c..00747e0939 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -37,6 +37,18 @@ select col1, col2, char_length(col3) from inserttest;
 
 drop table inserttest;
 
+--
+-- SET test
+--
+create table inserttest (col1 int4, col2 text default 'bar');
+insert into inserttest set col1 = 1, col2 = 'foo';
+insert into inserttest set col1 = 2, col2 = DEFAULT;
+insert into inserttest set (col1, col2) = (3, 'baz');
+
+select * from inserttest;
+
+drop table inserttest;
+
 --
 -- check indirection (field/array assignment), cf bug #14265
 --
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 43691cd335..e0eb2df0c8 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index f85645efde..e6d2e13714 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -406,6 +406,16 @@ SELECT * FROM y;
 
 DROP TABLE y;
 
+CREATE TEMPORARY TABLE y (a INTEGER);
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
+DROP TABLE y;
+
 --
 -- error cases
 --
-- 
2.17.1

From 44ff9500dfa14994047d2d64279259c39360b6b9 Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth@internetnz.net.nz>
Date: Mon, 26 Aug 2019 14:39:16 +1200
Subject: [PATCH] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values can also be sourced from other tables similar to the INSERT INTO
SELECT FROM syntax:

INSERT INTO t SET c1 = x.c1, c2 = x.c2 FROM x WHERE x.c2 > 10 LIMIT 10;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 52 +++++++++++++++++++
 src/backend/parser/gram.y                     | 50 +++++++++++++++++-
 src/backend/parser/parse_expr.c               |  9 +++-
 src/test/regress/expected/insert.out          | 38 ++++++++++++++
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 21 ++++++++
 src/test/regress/sql/insert.sql               | 30 +++++++++++
 src/test/regress/sql/insert_conflict.sql      |  3 ++
 src/test/regress/sql/with.sql                 | 10 ++++
 9 files changed, 212 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index f995a7637f..71fab5126d 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -28,6 +28,25 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ 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> ] [, ...] ]
 
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
+    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> )
+        } [, ...]
+    [ FROM <replaceable class="parameter">from_list</replaceable>
+           [ WHERE <replaceable class="parameter">condition</replaceable> ] 
+           [ GROUP BY <replaceable class="parameter">grouping_element</replaceable> [, ...] ]
+           [ HAVING <replaceable class="parameter">condition</replaceable> [, ...] ]
+           [ WINDOW <replaceable class="parameter">window_name</replaceable> AS ( <replaceable class="parameter">window_definition</replaceable> ) [, ...] ]
+           [ ORDER BY <replaceable class="parameter">expression</replaceable> [ ASC | DESC | USING <replaceable class="parameter">operator</replaceable> ] [ NULLS { FIRST | LAST } ] [, ...] ]
+           [ LIMIT { <replaceable class="parameter">count</replaceable> | ALL } ]
+           [ OFFSET <replaceable class="parameter">start</replaceable> [ ROW | ROWS ] ] ]
+           [ FETCH { FIRST | NEXT } [ <replaceable class="parameter">count</replaceable> ] { ROW | ROWS } ONLY ]
+    [ 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> ]
@@ -254,6 +273,32 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_list</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
+      <term><replaceable class="parameter">condition</replaceable></term>
+      <term><replaceable class="parameter">grouping_element</replaceable></term>
+      <term><replaceable class="parameter">window_name</replaceable></term>
+      <term><replaceable class="parameter">count</replaceable></term>
+      <term><replaceable class="parameter">start</replaceable></term>
+      <listitem>
+       <para>
+        These have the same meaning as when used in a query
+        (<command>SELECT</command> statement).  Refer to the
+        <xref linkend="sql-select"/> statement for a description of the
+        syntax.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -675,6 +720,13 @@ WITH upd AS (
     RETURNING *
 )
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
+</programlisting>
+  </para>
+  <para>
+   Insert a single row into table <literal>distributors</literal> using a
+<literal>SET</literal> clause to specify the columns and values:
+<programlisting>
+INSERT INTO distributors SET did = 4, dname = 'Hammers Unlimited, LLC';
 </programlisting>
   </para>
   <para>
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c97bb367f8..9c907dfd1b 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -463,7 +463,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 
@@ -10892,6 +10892,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -10923,6 +10932,45 @@ insert_column_item:
 				}
 		;
 
+insert_set_clause:
+		SET set_clause_list
+			{
+				SelectStmt *n = makeNode(SelectStmt);
+				List *values = NIL;
+				ListCell *col_cell;
+
+				foreach(col_cell, $2)
+				{
+					ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+
+					values = lappend(values, res_col->val);
+				}
+				n->valuesLists = list_make1(values);
+				$$ = makeNode(InsertStmt);
+				$$->cols = $2;
+				$$->selectStmt = (Node *) n;
+			}
+		| SET set_clause_list FROM from_list where_clause
+		  group_clause having_clause window_clause opt_sort_clause
+		  opt_select_limit
+			{
+				SelectStmt *n = makeNode(SelectStmt);
+				n->targetList = $2;
+				n->fromClause = $4;
+				n->whereClause = $5;
+				n->groupClause = $6;
+				n->havingClause = $7;
+				n->windowClause = $8;
+				insertSelectOptions(n, $9, NULL,
+									list_nth($10, 0), list_nth($10, 1),
+									NULL,
+									yyscanner);
+				$$ = makeNode(InsertStmt);
+				$$->cols = $2;
+				$$->selectStmt = (Node *) n;
+			}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 76f3dd7076..91bf1530b2 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1566,8 +1566,13 @@ transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref)
 	Query	   *qtree;
 	TargetEntry *tle;
 
-	/* We should only see this in first-stage processing of UPDATE tlists */
-	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE);
+	/*
+	 * We should only see this in first-stage processing of UPDATE tlists
+	 * or of an INSERT SET tlist.
+	 */
+	Assert(pstate->p_expr_kind == EXPR_KIND_VALUES_SINGLE ||
+		   pstate->p_expr_kind == EXPR_KIND_INSERT_TARGET ||
+		   pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE);
 
 	/* We only need to transform the source if this is the first column */
 	if (maref->colno == 1)
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 75e25cdf48..6a934d1873 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -82,6 +82,44 @@ select col1, col2, char_length(col3) from inserttest;
 
 drop table inserttest;
 --
+-- SET test
+--
+create table inserttest (col1 int4, col2 text default 'bar');
+insert into inserttest set col1 = 1, col2 = 'foo';
+insert into inserttest set col1 = 2, col2 = DEFAULT;
+insert into inserttest set (col1, col2) = (3, 'baz');
+select * from inserttest;
+ col1 | col2 
+------+------
+    1 | foo
+    2 | bar
+    3 | baz
+(3 rows)
+
+truncate inserttest;
+create table inserttest2 (col1 int4, col2 text default 'f');
+insert into inserttest2 values (10, 't'), (20, 'f'), (50, 't');
+insert into inserttest set col1 = inserttest2.col1 from inserttest2
+  where inserttest2.col1 > 10 order by inserttest2.col1 desc limit 1;
+select * from inserttest;
+ col1 | col2 
+------+------
+   50 | bar
+(1 row)
+
+truncate inserttest;
+insert into inserttest set col1 = sum(inserttest2.col1) from inserttest2
+  group by inserttest2.col2;
+select * from inserttest;
+ col1 | col2 
+------+------
+   60 | bar
+   20 | bar
+(2 rows)
+
+drop table inserttest;
+drop table inserttest2;
+--
 -- check indirection (field/array assignment), cf bug #14265
 --
 -- these tests are aware that transformInsertStmt has 3 separate code paths
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e..4d6538b1fb 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 2a2085556b..b92f9f8a13 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -952,6 +952,27 @@ SELECT * FROM y;
  10
 (10 rows)
 
+DROP TABLE y;
+CREATE TEMPORARY TABLE y (a INTEGER);
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 23885f638c..c00a23322b 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -37,6 +37,36 @@ select col1, col2, char_length(col3) from inserttest;
 
 drop table inserttest;
 
+--
+-- SET test
+--
+create table inserttest (col1 int4, col2 text default 'bar');
+insert into inserttest set col1 = 1, col2 = 'foo';
+insert into inserttest set col1 = 2, col2 = DEFAULT;
+insert into inserttest set (col1, col2) = (3, 'baz');
+
+select * from inserttest;
+
+truncate inserttest;
+
+create table inserttest2 (col1 int4, col2 text default 'f');
+insert into inserttest2 values (10, 't'), (20, 'f'), (50, 't');
+
+insert into inserttest set col1 = inserttest2.col1 from inserttest2
+  where inserttest2.col1 > 10 order by inserttest2.col1 desc limit 1;
+
+select * from inserttest;
+
+truncate inserttest;
+
+insert into inserttest set col1 = sum(inserttest2.col1) from inserttest2
+  group by inserttest2.col2;
+
+select * from inserttest;
+
+drop table inserttest;
+drop table inserttest2;
+
 --
 -- check indirection (field/array assignment), cf bug #14265
 --
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 43691cd335..e0eb2df0c8 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index f85645efde..e6d2e13714 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -406,6 +406,16 @@ SELECT * FROM y;
 
 DROP TABLE y;
 
+CREATE TEMPORARY TABLE y (a INTEGER);
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
+DROP TABLE y;
+
 --
 -- error cases
 --
-- 
2.17.1

From 16595f1ac8cfa183061586711c98ef2c9a622e71 Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth@internetnz.net.nz>
Date: Thu, 21 Nov 2019 16:40:39 +1300
Subject: [PATCH] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values can also be sourced from other tables similar to the INSERT INTO
SELECT FROM syntax:

INSERT INTO t SET c1 = x.c1, c2 = x.c2 FROM x WHERE x.c2 > 10 LIMIT 10;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 59 ++++++++++++++++++-
 src/backend/parser/gram.y                     | 58 +++++++++++++++++-
 src/backend/parser/parse_expr.c               | 10 +++-
 src/test/regress/expected/insert.out          | 26 +++++---
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 20 +++++++
 src/test/regress/sql/insert.sql               |  2 +
 src/test/regress/sql/insert_conflict.sql      |  3 +
 src/test/regress/sql/with.sql                 |  9 +++
 9 files changed, 177 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index f995a7637f..728edb9bda 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -28,6 +28,19 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ 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> ] [, ...] ]
 
+
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
+    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> )
+        } [, ...]
+    [ FROM <replaceable class="parameter">from_clause</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> ]
@@ -254,6 +267,18 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_clause</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+        Refer to the <xref linkend="sql-select"/> statement for a
+        description of the syntax.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -631,6 +656,15 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES
 </programlisting>
   </para>
 
+  <para>
+    Insert a row using <command>SET</command> syntax:
+
+<programlisting>
+INSERT INTO films SET code = 'MH832', title = 'Blade Runner',
+    did = 201, date_prod = DEFAULT, kind = 'SciFi'; 
+</programlisting>
+  </para>
+
   <para>
    This example inserts some rows into table
    <literal>films</literal> from a table <literal>tmp_films</literal>
@@ -677,6 +711,16 @@ WITH upd AS (
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
 </programlisting>
   </para>
+  <para>
+   Insert multiple rows into <literal>employees_log</literal> containing
+the hours worked by each employee from <literal>time_sheets</literal>.
+<programlisting>
+INSERT INTO employees_log SET id = time_sheets.employee,
+    total_hours = sum(time_sheets.hours) FROM time_sheets
+    WHERE time_sheets.date &ge; '2019-11-15' GROUP BY time_sheets.employee;
+</programlisting>
+  </para>
+
   <para>
    Insert or update new distributors as appropriate.  Assumes a unique
    index has been defined that constrains values appearing in the
@@ -733,6 +777,18 @@ INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
     ON CONFLICT (did) WHERE is_active DO NOTHING;
 </programlisting></para>
+  <para>
+   Insert a new film into <literal>watched_films</literal> or increment the
+   number of times seen. Returns the new seen count, example assumes a
+   unique index has been defined that constrains the values appearing in
+   the <literal>title</title> and <literal>year</literal> columns and 
+   that <literal>seen_count</literal> defaults to 1.
+<programlisting>
+INSERT INTO watched_films SET title = 'Akira', year = 1988
+   ON CONFLICT (title, year) DO UPDATE SET seen_count = watched_films.seen_count + 1
+   RETURNING watched_films.seen_count;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
@@ -743,7 +799,8 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    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>.
+   specify an alternative action with <literal>ON CONFLICT</literal>, and the
+   ability to specify the inserted columns using <literal>SET</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>,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 2f7bd662e8..57633260e9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -465,7 +465,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 
@@ -10962,6 +10962,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -10993,6 +11002,50 @@ insert_column_item:
 				}
 		;
 
+/*
+ * There are two rules here to handle the two different types of INSERT.
+ * INSERT using VALUES and INSERT using SELECT. They can't be combined
+ * because only the VALUES syntax allows specifying DEFAULT.
+ */
+insert_set_clause:
+		SET set_clause_list
+			{
+				SelectStmt *n = makeNode(SelectStmt);
+				List *values = NIL;
+				ListCell *col_cell;
+
+				foreach(col_cell, $2)
+				{
+					ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+
+					values = lappend(values, res_col->val);
+				}
+				n->valuesLists = list_make1(values);
+				$$ = makeNode(InsertStmt);
+				$$->cols = $2;
+				$$->selectStmt = (Node *) n;
+			}
+		| SET set_clause_list FROM from_list where_clause group_clause
+		having_clause window_clause opt_sort_clause opt_select_limit
+		opt_for_locking_clause
+			{
+				SelectStmt *n = makeNode(SelectStmt);
+				n->targetList = $2;
+				n->fromClause = $4;
+				n->whereClause = $5;
+				n->groupClause = $6;
+				n->havingClause = $7;
+				n->windowClause = $8;
+				insertSelectOptions(n, $9, $11,
+									list_nth($10, 0), list_nth($10, 1),
+									NULL,
+									yyscanner);
+				$$ = makeNode(InsertStmt);
+				$$->cols = $2;
+				$$->selectStmt = (Node *) n;
+			}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
@@ -11376,6 +11429,9 @@ select_clause:
  *
  * NOTE: only the leftmost component SelectStmt should have INTO.
  * However, this is not checked by the grammar; parse analysis must check it.
+ *
+ * NOTE: insert_set_clause also has SELECT-like syntax so if you add any
+ * clauses after from_clause here you may need to add them there as well.
  */
 simple_select:
 			SELECT opt_all_clause opt_target_list
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index eb91da2d87..dc96ce76e4 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1565,8 +1565,14 @@ transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref)
 	Query	   *qtree;
 	TargetEntry *tle;
 
-	/* We should only see this in first-stage processing of UPDATE tlists */
-	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE);
+	/*
+	 * We should only see this in first-stage processing of UPDATE tlists
+	 * (UPDATE_SOURCE), an INSERT SET tlist (VALUES_SINGLE) or from a rewritten
+	 * query using rules (SELECT_TARGET).
+	 */
+	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE ||
+		   pstate->p_expr_kind == EXPR_KIND_VALUES_SINGLE ||
+		   pstate->p_expr_kind == EXPR_KIND_SELECT_TARGET);
 
 	/* We only need to transform the source if this is the first column */
 	if (maref->colno == 1)
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 75e25cdf48..1492ae40ee 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -9,14 +9,18 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
+insert into inserttest set (col1, col2, col3) = (DEFAULT, 11, 'more testing');
 select * from inserttest;
- col1 | col2 |  col3   
-------+------+---------
+ col1 | col2 |     col3     
+------+------+--------------
       |    3 | testing
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+      |   11 | more testing
+(6 rows)
 
 --
 -- insert with similar expression / target_list values (all fail)
@@ -38,13 +42,15 @@ ERROR:  INSERT has more expressions than target columns
 LINE 1: insert into inserttest (col1) values (DEFAULT, DEFAULT);
                                                        ^
 select * from inserttest;
- col1 | col2 |  col3   
-------+------+---------
+ col1 | col2 |     col3     
+------+------+--------------
       |    3 | testing
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+      |   11 | more testing
+(6 rows)
 
 --
 -- VALUES test
@@ -58,10 +64,12 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
+      |    9 | testing
+      |   11 | more testing
    10 |   20 | 40
    -1 |    2 | testing
     2 |    3 | values are fun!
-(7 rows)
+(9 rows)
 
 --
 -- TOASTed value test
@@ -74,11 +82,13 @@ select col1, col2, char_length(col3) from inserttest;
       |    5 |           7
       |    5 |           4
       |    7 |           7
+      |    9 |           7
+      |   11 |          12
    10 |   20 |           2
    -1 |    2 |           7
     2 |    3 |          15
    30 |   50 |       10000
-(8 rows)
+(10 rows)
 
 drop table inserttest;
 --
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e..4d6538b1fb 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 2a2085556b..a1236d8683 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -952,6 +952,26 @@ SELECT * FROM y;
  10
 (10 rows)
 
+TRUNCATE TABLE y;
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 23885f638c..bc903d3316 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -7,6 +7,8 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
+insert into inserttest set (col1, col2, col3) = (DEFAULT, 11, 'more testing');
 
 select * from inserttest;
 
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 43691cd335..e0eb2df0c8 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index f85645efde..d3e04bbad9 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -404,6 +404,15 @@ DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
 
 SELECT * FROM y;
 
+TRUNCATE TABLE y;
+
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
 DROP TABLE y;
 
 --
-- 
2.17.1

From c7c32435f0c0a1948e5c3ebd7d66f0bc415ee54e Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth@internetnz.net.nz>
Date: Mon, 2 Dec 2019 10:59:40 +0000
Subject: [PATCH] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values can also be sourced from other tables similar to the INSERT INTO
SELECT FROM syntax:

INSERT INTO t SET c1 = x.c1, c2 = x.c2 FROM x WHERE x.c2 > 10 LIMIT 10;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 59 ++++++++++++++++++-
 src/backend/parser/gram.y                     | 58 +++++++++++++++++-
 src/backend/parser/parse_expr.c               | 10 +++-
 src/test/regress/expected/insert.out          | 26 +++++---
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 20 +++++++
 src/test/regress/sql/insert.sql               |  2 +
 src/test/regress/sql/insert_conflict.sql      |  3 +
 src/test/regress/sql/with.sql                 |  9 +++
 9 files changed, 177 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index e829c61642..b2f7c06f53 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -28,6 +28,19 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ 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> ] [, ...] ]
 
+
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
+    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> )
+        } [, ...]
+    [ FROM <replaceable class="parameter">from_clause</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> ]
@@ -254,6 +267,18 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_clause</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+        Refer to the <xref linkend="sql-select"/> statement for a
+        description of the syntax.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -631,6 +656,15 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES
 </programlisting>
   </para>
 
+  <para>
+    Insert a row using <command>SET</command> syntax:
+
+<programlisting>
+INSERT INTO films SET code = 'MH832', title = 'Blade Runner',
+    did = 201, date_prod = DEFAULT, kind = 'SciFi'; 
+</programlisting>
+  </para>
+
   <para>
    This example inserts some rows into table
    <literal>films</literal> from a table <literal>tmp_films</literal>
@@ -677,6 +711,16 @@ WITH upd AS (
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
 </programlisting>
   </para>
+  <para>
+   Insert multiple rows into <literal>employees_log</literal> containing
+the hours worked by each employee from <literal>time_sheets</literal>.
+<programlisting>
+INSERT INTO employees_log SET id = time_sheets.employee,
+    total_hours = sum(time_sheets.hours) FROM time_sheets
+    WHERE time_sheets.date &ge; '2019-11-15' GROUP BY time_sheets.employee;
+</programlisting>
+  </para>
+
   <para>
    Insert or update new distributors as appropriate.  Assumes a unique
    index has been defined that constrains values appearing in the
@@ -733,6 +777,18 @@ INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
     ON CONFLICT (did) WHERE is_active DO NOTHING;
 </programlisting></para>
+  <para>
+   Insert a new film into <literal>watched_films</literal> or increment the
+   number of times seen. Returns the new seen count, example assumes a
+   unique index has been defined that constrains the values appearing in
+   the <literal>title</literal> and <literal>year</literal> columns and 
+   that <literal>seen_count</literal> defaults to 1.
+<programlisting>
+INSERT INTO watched_films SET title = 'Akira', year = 1988
+   ON CONFLICT (title, year) DO UPDATE SET seen_count = watched_films.seen_count + 1
+   RETURNING watched_films.seen_count;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
@@ -743,7 +799,8 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    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>.
+   specify an alternative action with <literal>ON CONFLICT</literal>, and the
+   ability to specify the inserted columns using <literal>SET</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>,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c5086846de..dc825c64bc 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -465,7 +465,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 
@@ -10984,6 +10984,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -11015,6 +11024,50 @@ insert_column_item:
 				}
 		;
 
+/*
+ * There are two rules here to handle the two different types of INSERT.
+ * INSERT using VALUES and INSERT using SELECT. They can't be combined
+ * because only the VALUES syntax allows specifying DEFAULT.
+ */
+insert_set_clause:
+		SET set_clause_list
+			{
+				SelectStmt *n = makeNode(SelectStmt);
+				List *values = NIL;
+				ListCell *col_cell;
+
+				foreach(col_cell, $2)
+				{
+					ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+
+					values = lappend(values, res_col->val);
+				}
+				n->valuesLists = list_make1(values);
+				$$ = makeNode(InsertStmt);
+				$$->cols = $2;
+				$$->selectStmt = (Node *) n;
+			}
+		| SET set_clause_list FROM from_list where_clause group_clause
+		having_clause window_clause opt_sort_clause opt_select_limit
+		opt_for_locking_clause
+			{
+				SelectStmt *n = makeNode(SelectStmt);
+				n->targetList = $2;
+				n->fromClause = $4;
+				n->whereClause = $5;
+				n->groupClause = $6;
+				n->havingClause = $7;
+				n->windowClause = $8;
+				insertSelectOptions(n, $9, $11,
+									list_nth($10, 0), list_nth($10, 1),
+									NULL,
+									yyscanner);
+				$$ = makeNode(InsertStmt);
+				$$->cols = $2;
+				$$->selectStmt = (Node *) n;
+			}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
@@ -11398,6 +11451,9 @@ select_clause:
  *
  * NOTE: only the leftmost component SelectStmt should have INTO.
  * However, this is not checked by the grammar; parse analysis must check it.
+ *
+ * NOTE: insert_set_clause also has SELECT-like syntax so if you add any
+ * clauses after from_clause here you may need to add them there as well.
  */
 simple_select:
 			SELECT opt_all_clause opt_target_list
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index eb91da2d87..dc96ce76e4 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1565,8 +1565,14 @@ transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref)
 	Query	   *qtree;
 	TargetEntry *tle;
 
-	/* We should only see this in first-stage processing of UPDATE tlists */
-	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE);
+	/*
+	 * We should only see this in first-stage processing of UPDATE tlists
+	 * (UPDATE_SOURCE), an INSERT SET tlist (VALUES_SINGLE) or from a rewritten
+	 * query using rules (SELECT_TARGET).
+	 */
+	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE ||
+		   pstate->p_expr_kind == EXPR_KIND_VALUES_SINGLE ||
+		   pstate->p_expr_kind == EXPR_KIND_SELECT_TARGET);
 
 	/* We only need to transform the source if this is the first column */
 	if (maref->colno == 1)
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 75e25cdf48..1492ae40ee 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -9,14 +9,18 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
+insert into inserttest set (col1, col2, col3) = (DEFAULT, 11, 'more testing');
 select * from inserttest;
- col1 | col2 |  col3   
-------+------+---------
+ col1 | col2 |     col3     
+------+------+--------------
       |    3 | testing
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+      |   11 | more testing
+(6 rows)
 
 --
 -- insert with similar expression / target_list values (all fail)
@@ -38,13 +42,15 @@ ERROR:  INSERT has more expressions than target columns
 LINE 1: insert into inserttest (col1) values (DEFAULT, DEFAULT);
                                                        ^
 select * from inserttest;
- col1 | col2 |  col3   
-------+------+---------
+ col1 | col2 |     col3     
+------+------+--------------
       |    3 | testing
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+      |   11 | more testing
+(6 rows)
 
 --
 -- VALUES test
@@ -58,10 +64,12 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
+      |    9 | testing
+      |   11 | more testing
    10 |   20 | 40
    -1 |    2 | testing
     2 |    3 | values are fun!
-(7 rows)
+(9 rows)
 
 --
 -- TOASTed value test
@@ -74,11 +82,13 @@ select col1, col2, char_length(col3) from inserttest;
       |    5 |           7
       |    5 |           4
       |    7 |           7
+      |    9 |           7
+      |   11 |          12
    10 |   20 |           2
    -1 |    2 |           7
     2 |    3 |          15
    30 |   50 |       10000
-(8 rows)
+(10 rows)
 
 drop table inserttest;
 --
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e..4d6538b1fb 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 2a2085556b..a1236d8683 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -952,6 +952,26 @@ SELECT * FROM y;
  10
 (10 rows)
 
+TRUNCATE TABLE y;
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 23885f638c..bc903d3316 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -7,6 +7,8 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
+insert into inserttest set (col1, col2, col3) = (DEFAULT, 11, 'more testing');
 
 select * from inserttest;
 
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 43691cd335..e0eb2df0c8 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index f85645efde..d3e04bbad9 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -404,6 +404,15 @@ DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
 
 SELECT * FROM y;
 
+TRUNCATE TABLE y;
+
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
 DROP TABLE y;
 
 --
-- 
2.17.1

From 488b267335dd17a4cc51cc281ffb8e0d32fbee09 Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth@internetnz.net.nz>
Date: Wed, 25 Mar 2020 18:57:58 +1300
Subject: [PATCH] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values can also be sourced from other tables similar to the INSERT INTO
SELECT FROM syntax:

INSERT INTO t SET c1 = x.c1, c2 = x.c2 FROM x WHERE x.c2 > 10 LIMIT 10;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 56 +++++++++++++++-
 src/backend/parser/gram.y                     | 65 ++++++++++++++++++-
 src/backend/parser/parse_expr.c               |  8 ++-
 src/test/regress/expected/insert.out          | 13 ++--
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 20 ++++++
 src/test/regress/sql/insert.sql               |  1 +
 src/test/regress/sql/insert_conflict.sql      |  3 +
 src/test/regress/sql/with.sql                 |  9 +++
 9 files changed, 169 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index e829c61642..3542f98d02 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -28,6 +28,16 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ 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> ] [, ...] ]
 
+
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
+    SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } } [, ...]
+    [ FROM <replaceable class="parameter">from_clause</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> ]
@@ -254,6 +264,18 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_clause</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+        Refer to the <xref linkend="sql-select"/> statement for a
+        description of the syntax.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -631,6 +653,15 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES
 </programlisting>
   </para>
 
+  <para>
+    Insert a row using <command>SET</command> syntax:
+
+<programlisting>
+INSERT INTO films SET code = 'MH832', title = 'Blade Runner',
+    did = 201, date_prod = DEFAULT, kind = 'SciFi'; 
+</programlisting>
+  </para>
+
   <para>
    This example inserts some rows into table
    <literal>films</literal> from a table <literal>tmp_films</literal>
@@ -677,6 +708,16 @@ WITH upd AS (
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
 </programlisting>
   </para>
+  <para>
+   Insert multiple rows into <literal>employees_log</literal> containing
+the hours worked by each employee from <literal>time_sheets</literal>.
+<programlisting>
+INSERT INTO employees_log SET id = time_sheets.employee,
+    total_hours = sum(time_sheets.hours) FROM time_sheets
+    WHERE time_sheets.date &ge; '2019-11-15' GROUP BY time_sheets.employee;
+</programlisting>
+  </para>
+
   <para>
    Insert or update new distributors as appropriate.  Assumes a unique
    index has been defined that constrains values appearing in the
@@ -733,6 +774,18 @@ INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
     ON CONFLICT (did) WHERE is_active DO NOTHING;
 </programlisting></para>
+  <para>
+   Insert a new film into <literal>watched_films</literal> or increment the
+   number of times seen. Returns the new seen count, example assumes a
+   unique index has been defined that constrains the values appearing in
+   the <literal>title</literal> and <literal>year</literal> columns and 
+   that <literal>seen_count</literal> defaults to 1.
+<programlisting>
+INSERT INTO watched_films SET title = 'Akira', year = 1988
+   ON CONFLICT (title, year) DO UPDATE SET seen_count = watched_films.seen_count + 1
+   RETURNING watched_films.seen_count;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
@@ -743,7 +796,8 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    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>.
+   specify an alternative action with <literal>ON CONFLICT</literal>, and the
+   ability to specify the inserted columns using <literal>SET</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>,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7e384f956c..31a02389a0 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -465,7 +465,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 
@@ -11029,6 +11029,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -11060,6 +11069,57 @@ insert_column_item:
 				}
 		;
 
+insert_set_clause:
+		SET set_clause_list from_clause where_clause group_clause
+		having_clause window_clause opt_sort_clause opt_select_limit
+		opt_for_locking_clause
+			{
+				SelectStmt *n = makeNode(SelectStmt);
+				List *values = NIL;
+				ListCell *col_cell;
+				ResTarget *res_col;
+
+				foreach(col_cell, $2)
+				{
+					res_col = (ResTarget *) lfirst(col_cell);
+
+					if (IsA(res_col->val, MultiAssignRef))
+						ereport(ERROR,
+							(errcode(ERRCODE_SYNTAX_ERROR),
+							 errmsg("INSERT SET does not support multi-assignment of columns."),
+							 errhint("Specify the column assignments separately."),
+							 parser_errposition(@2)));
+				}
+
+				if ($3 == NULL)
+				{
+					foreach(col_cell, $2)
+					{
+						res_col = (ResTarget *) lfirst(col_cell);
+						values = lappend(values, res_col->val);
+					}
+					n->valuesLists = list_make1(values);
+				}
+				else
+				{
+					n->targetList = $2;
+					n->fromClause = $3;
+				}
+
+				n->whereClause = $4;
+				n->groupClause = $5;
+				n->havingClause = $6;
+				n->windowClause = $7;
+				insertSelectOptions(n, $8, $10,
+									list_nth($9, 0), list_nth($9, 1),
+									NULL,
+									yyscanner);
+				$$ = makeNode(InsertStmt);
+				$$->cols = $2;
+				$$->selectStmt = (Node *) n;
+			}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
@@ -11443,6 +11503,9 @@ select_clause:
  *
  * NOTE: only the leftmost component SelectStmt should have INTO.
  * However, this is not checked by the grammar; parse analysis must check it.
+ *
+ * NOTE: insert_set_clause also has SELECT-like syntax so if you add any
+ * clauses after from_clause here you may need to add them there as well.
  */
 simple_select:
 			SELECT opt_all_clause opt_target_list
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 831db4af95..644f1919fd 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1572,8 +1572,12 @@ transformMultiAssignRef(ParseState *pstate, MultiAssignRef *maref)
 	Query	   *qtree;
 	TargetEntry *tle;
 
-	/* We should only see this in first-stage processing of UPDATE tlists */
-	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE);
+	/*
+	 * We should only see this in first-stage processing of UPDATE tlists
+	 * (UPDATE_SOURCE), an INSERT SET tlist (VALUES_SINGLE).
+	 */
+	Assert(pstate->p_expr_kind == EXPR_KIND_UPDATE_SOURCE ||
+		   pstate->p_expr_kind == EXPR_KIND_VALUES_SINGLE);
 
 	/* We only need to transform the source if this is the first column */
 	if (maref->colno == 1)
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 45d77ba3a5..057147d76d 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -9,6 +9,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 select * from inserttest;
  col1 | col2 |  col3   
 ------+------+---------
@@ -16,7 +17,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- insert with similar expression / target_list values (all fail)
@@ -44,7 +46,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- VALUES test
@@ -58,10 +61,11 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
+      |    9 | testing
    10 |   20 | 40
    -1 |    2 | testing
     2 |    3 | values are fun!
-(7 rows)
+(8 rows)
 
 --
 -- TOASTed value test
@@ -74,11 +78,12 @@ select col1, col2, char_length(col3) from inserttest;
       |    5 |           7
       |    5 |           4
       |    7 |           7
+      |    9 |           7
    10 |   20 |           2
    -1 |    2 |           7
     2 |    3 |          15
    30 |   50 |       10000
-(8 rows)
+(9 rows)
 
 drop table inserttest;
 --
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e..4d6538b1fb 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 67eaeb4f3e..7ec234b186 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -952,6 +952,26 @@ SELECT * FROM y;
  10
 (10 rows)
 
+TRUNCATE TABLE y;
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 23885f638c..0306669167 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -7,6 +7,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 
 select * from inserttest;
 
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 43691cd335..e0eb2df0c8 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index f85645efde..d3e04bbad9 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -404,6 +404,15 @@ DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
 
 SELECT * FROM y;
 
+TRUNCATE TABLE y;
+
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
 DROP TABLE y;
 
 --
-- 
2.17.1

From 12f361865276439b513cd1c17197889334b1b2b6 Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth@internetnz.net.nz>
Date: Thu, 23 Apr 2020 03:24:48 +0000
Subject: [PATCH] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values can also be sourced from other tables similar to the INSERT INTO
SELECT FROM syntax:

INSERT INTO t SET c1 = x.c1, c2 = x.c2 FROM x WHERE x.c2 > 10 LIMIT 10;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 56 +++++++++++++-
 src/backend/parser/gram.y                     | 76 ++++++++++++++++++-
 src/test/regress/expected/insert.out          | 13 +++-
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 20 +++++
 src/test/regress/sql/insert.sql               |  1 +
 src/test/regress/sql/insert_conflict.sql      |  3 +
 src/test/regress/sql/with.sql                 |  9 +++
 8 files changed, 172 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a6cec6b02e..07930da71f 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -28,6 +28,16 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ 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> ] [, ...] ]
 
+
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
+    SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } } [, ...]
+    [ FROM <replaceable class="parameter">from_clause</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> ]
@@ -264,6 +274,18 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_clause</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+        Refer to the <xref linkend="sql-select"/> statement for a
+        description of the syntax.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -644,6 +666,15 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES
 </programlisting>
   </para>
 
+  <para>
+    Insert a row using <command>SET</command> syntax:
+
+<programlisting>
+INSERT INTO films SET code = 'MH832', title = 'Blade Runner',
+    did = 201, date_prod = DEFAULT, kind = 'SciFi'; 
+</programlisting>
+  </para>
+
   <para>
    This example inserts some rows into table
    <literal>films</literal> from a table <literal>tmp_films</literal>
@@ -690,6 +721,16 @@ WITH upd AS (
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
 </programlisting>
   </para>
+  <para>
+   Insert multiple rows into <literal>employees_log</literal> containing
+the hours worked by each employee from <literal>time_sheets</literal>.
+<programlisting>
+INSERT INTO employees_log SET id = time_sheets.employee,
+    total_hours = sum(time_sheets.hours) FROM time_sheets
+    WHERE time_sheets.date &ge; '2019-11-15' GROUP BY time_sheets.employee;
+</programlisting>
+  </para>
+
   <para>
    Insert or update new distributors as appropriate.  Assumes a unique
    index has been defined that constrains values appearing in the
@@ -746,6 +787,18 @@ INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
     ON CONFLICT (did) WHERE is_active DO NOTHING;
 </programlisting></para>
+  <para>
+   Insert a new film into <literal>watched_films</literal> or increment the
+   number of times seen. Returns the new seen count, example assumes a
+   unique index has been defined that constrains the values appearing in
+   the <literal>title</literal> and <literal>year</literal> columns and 
+   that <literal>seen_count</literal> defaults to 1.
+<programlisting>
+INSERT INTO watched_films SET title = 'Akira', year = 1988
+   ON CONFLICT (title, year) DO UPDATE SET seen_count = watched_films.seen_count + 1
+   RETURNING watched_films.seen_count;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
@@ -756,7 +809,8 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    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>.
+   specify an alternative action with <literal>ON CONFLICT</literal>, and the
+   ability to specify the inserted columns using <literal>SET</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>,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3c78f2d1b5..1997d345a2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -402,7 +402,7 @@ 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
+				set_clause_list set_clause insert_set_list
 				def_list operator_def_list indirection opt_indirection
 				reloption_list group_clause TriggerFuncArgs opclass_item_list opclass_drop_list
 				opclass_purpose opt_opfamily transaction_mode_list_or_empty
@@ -475,7 +475,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 
@@ -509,7 +509,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <range>	relation_expr
 %type <range>	relation_expr_opt_alias
 %type <node>	tablesample_clause opt_repeatable_clause
-%type <target>	target_el set_target insert_column_item
+%type <target>	target_el set_target insert_column_item insert_set_item
 
 %type <str>		generic_option_name
 %type <node>	generic_option_arg
@@ -11064,6 +11064,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -11095,6 +11104,64 @@ insert_column_item:
 				}
 		;
 
+/*
+ * There are two rules here to handle the two different types of INSERT.
+ * INSERT using VALUES and INSERT using SELECT. They can't be combined
+ * because only the VALUES syntax allows specifying DEFAULT.
+ */
+insert_set_clause:
+			SET insert_set_list
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+					ListCell *col_cell;
+					List *values = NIL;
+
+					foreach(col_cell, $2)
+					{
+						ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+
+						values = lappend(values, res_col->val);
+					}
+
+					n->valuesLists = list_make1(values);
+					$$ = makeNode(InsertStmt);
+					$$->cols = $2;
+					$$->selectStmt = (Node *) n;
+				}
+			| SET insert_set_list FROM from_list where_clause group_clause
+			having_clause window_clause opt_sort_clause opt_select_limit
+			opt_for_locking_clause
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+
+					n->targetList = $2;
+					n->fromClause = $4;
+					n->whereClause = $5;
+					n->groupClause = $6;
+					n->havingClause = $7;
+					n->windowClause = $8;
+					insertSelectOptions(n, $9, $11, $10, NULL, yyscanner);
+					$$ = makeNode(InsertStmt);
+					$$->cols = $2;
+					$$->selectStmt = (Node *) n;
+				}
+		;
+
+insert_set_list:
+			insert_set_item
+				{ $$ = list_make1($1); }
+			| insert_set_list ',' insert_set_item
+				{ $$ = lappend($1, $3); }
+		;
+
+insert_set_item:
+			insert_column_item '=' a_expr
+				{
+					$$ = $1;
+					$$->val = $3;
+				}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
@@ -11478,6 +11545,9 @@ select_clause:
  *
  * NOTE: only the leftmost component SelectStmt should have INTO.
  * However, this is not checked by the grammar; parse analysis must check it.
+ *
+ * NOTE: insert_set_clause also has SELECT-like syntax so if you add any
+ * clauses after from_clause here you may need to add them there as well.
  */
 simple_select:
 			SELECT opt_all_clause opt_target_list
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 45d77ba3a5..057147d76d 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -9,6 +9,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 select * from inserttest;
  col1 | col2 |  col3   
 ------+------+---------
@@ -16,7 +17,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- insert with similar expression / target_list values (all fail)
@@ -44,7 +46,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- VALUES test
@@ -58,10 +61,11 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
+      |    9 | testing
    10 |   20 | 40
    -1 |    2 | testing
     2 |    3 | values are fun!
-(7 rows)
+(8 rows)
 
 --
 -- TOASTed value test
@@ -74,11 +78,12 @@ select col1, col2, char_length(col3) from inserttest;
       |    5 |           7
       |    5 |           4
       |    7 |           7
+      |    9 |           7
    10 |   20 |           2
    -1 |    2 |           7
     2 |    3 |          15
    30 |   50 |       10000
-(8 rows)
+(9 rows)
 
 drop table inserttest;
 --
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e..4d6538b1fb 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 67eaeb4f3e..7ec234b186 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -952,6 +952,26 @@ SELECT * FROM y;
  10
 (10 rows)
 
+TRUNCATE TABLE y;
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index 23885f638c..0306669167 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -7,6 +7,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 
 select * from inserttest;
 
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 43691cd335..e0eb2df0c8 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index f85645efde..d3e04bbad9 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -404,6 +404,15 @@ DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
 
 SELECT * FROM y;
 
+TRUNCATE TABLE y;
+
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
 DROP TABLE y;
 
 --
-- 
2.17.1

From 7e222b4068e445a723f1692c5cdeec99d498a161 Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth.palmer3@gmail.com>
Date: Wed, 22 Sep 2021 05:09:28 +0000
Subject: [PATCH] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values can also be sourced from other tables similar to the INSERT INTO
SELECT FROM syntax:

INSERT INTO t SET c1 = x.c1, c2 = x.c2 FROM x WHERE x.c2 > 10 LIMIT 10;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 56 +++++++++++++-
 src/backend/parser/gram.y                     | 77 ++++++++++++++++++-
 src/test/regress/expected/insert.out          | 13 +++-
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 20 +++++
 src/test/regress/sql/insert.sql               |  1 +
 src/test/regress/sql/insert_conflict.sql      |  3 +
 src/test/regress/sql/with.sql                 |  9 +++
 8 files changed, 173 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 2973b72b81..63c0579d4b 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -28,6 +28,16 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ 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> ] [, ...] ]
 
+
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
+    SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } } [, ...]
+    [ FROM <replaceable class="parameter">from_clause</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> ]
@@ -263,6 +273,18 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_clause</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+        Refer to the <xref linkend="sql-select"/> statement for a
+        description of the syntax.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -643,6 +665,15 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES
 </programlisting>
   </para>
 
+  <para>
+    Insert a row using <command>SET</command> syntax:
+
+<programlisting>
+INSERT INTO films SET code = 'MH832', title = 'Blade Runner',
+    did = 201, date_prod = DEFAULT, kind = 'SciFi'; 
+</programlisting>
+  </para>
+
   <para>
    This example inserts some rows into table
    <literal>films</literal> from a table <literal>tmp_films</literal>
@@ -689,6 +720,16 @@ WITH upd AS (
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
 </programlisting>
   </para>
+  <para>
+   Insert multiple rows into <literal>employees_log</literal> containing
+the hours worked by each employee from <literal>time_sheets</literal>.
+<programlisting>
+INSERT INTO employees_log SET id = time_sheets.employee,
+    total_hours = sum(time_sheets.hours) FROM time_sheets
+    WHERE time_sheets.date &ge; '2019-11-15' GROUP BY time_sheets.employee;
+</programlisting>
+  </para>
+
   <para>
    Insert or update new distributors as appropriate.  Assumes a unique
    index has been defined that constrains values appearing in the
@@ -745,6 +786,18 @@ INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
     ON CONFLICT (did) WHERE is_active DO NOTHING;
 </programlisting></para>
+  <para>
+   Insert a new film into <literal>watched_films</literal> or increment the
+   number of times seen. Returns the new seen count, example assumes a
+   unique index has been defined that constrains the values appearing in
+   the <literal>title</literal> and <literal>year</literal> columns and 
+   that <literal>seen_count</literal> defaults to 1.
+<programlisting>
+INSERT INTO watched_films SET title = 'Akira', year = 1988
+   ON CONFLICT (title, year) DO UPDATE SET seen_count = watched_films.seen_count + 1
+   RETURNING watched_films.seen_count;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
@@ -755,7 +808,8 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    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>.
+   specify an alternative action with <literal>ON CONFLICT</literal>, and the
+   ability to specify the inserted columns using <literal>SET</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>,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e3068a374e..ea5f93fb33 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -412,7 +412,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				any_operator expr_list attrs
 				distinct_clause opt_distinct_clause
 				target_list opt_target_list insert_column_list set_target_list
-				set_clause_list set_clause
+				set_clause_list set_clause insert_set_list
 				def_list operator_def_list indirection opt_indirection
 				reloption_list TriggerFuncArgs opclass_item_list opclass_drop_list
 				opclass_purpose opt_opfamily transaction_mode_list_or_empty
@@ -483,7 +483,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 
@@ -519,7 +519,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <range>	relation_expr
 %type <range>	relation_expr_opt_alias
 %type <node>	tablesample_clause opt_repeatable_clause
-%type <target>	target_el set_target insert_column_item
+%type <target>	target_el set_target insert_column_item insert_set_item
 
 %type <str>		generic_option_name
 %type <node>	generic_option_arg
@@ -11037,6 +11037,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -11068,6 +11077,65 @@ insert_column_item:
 				}
 		;
 
+/*
+ * There are two rules here to handle the two different types of INSERT.
+ * INSERT using VALUES and INSERT using SELECT. They can't be combined
+ * because only the VALUES syntax allows specifying DEFAULT.
+ */
+insert_set_clause:
+			SET insert_set_list
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+					ListCell *col_cell;
+					List *values = NIL;
+
+					foreach(col_cell, $2)
+					{
+						ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+
+						values = lappend(values, res_col->val);
+					}
+
+					n->valuesLists = list_make1(values);
+					$$ = makeNode(InsertStmt);
+					$$->cols = $2;
+					$$->selectStmt = (Node *) n;
+				}
+			| SET insert_set_list FROM from_list where_clause group_clause
+			having_clause window_clause opt_sort_clause opt_select_limit
+			opt_for_locking_clause
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+
+					n->targetList = $2;
+					n->fromClause = $4;
+					n->whereClause = $5;
+					n->groupClause = ($6)->list;
+					n->groupDistinct = ($6)->distinct;
+					n->havingClause = $7;
+					n->windowClause = $8;
+					insertSelectOptions(n, $9, $11, $10, NULL, yyscanner);
+					$$ = makeNode(InsertStmt);
+					$$->cols = $2;
+					$$->selectStmt = (Node *) n;
+				}
+		;
+
+insert_set_list:
+			insert_set_item
+				{ $$ = list_make1($1); }
+			| insert_set_list ',' insert_set_item
+				{ $$ = lappend($1, $3); }
+		;
+
+insert_set_item:
+			insert_column_item '=' a_expr
+				{
+					$$ = $1;
+					$$->val = $3;
+				}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
@@ -11457,6 +11525,9 @@ select_clause:
  *
  * NOTE: only the leftmost component SelectStmt should have INTO.
  * However, this is not checked by the grammar; parse analysis must check it.
+ *
+ * NOTE: insert_set_clause also has SELECT-like syntax so if you add any
+ * clauses after from_clause here you may need to add them there as well.
  */
 simple_select:
 			SELECT opt_all_clause opt_target_list
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 5063a3dc22..231542a7cf 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -9,6 +9,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 select * from inserttest;
  col1 | col2 |  col3   
 ------+------+---------
@@ -16,7 +17,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- insert with similar expression / target_list values (all fail)
@@ -44,7 +46,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- VALUES test
@@ -58,10 +61,11 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
+      |    9 | testing
    10 |   20 | 40
    -1 |    2 | testing
     2 |    3 | values are fun!
-(7 rows)
+(8 rows)
 
 --
 -- TOASTed value test
@@ -74,11 +78,12 @@ select col1, col2, char_length(col3) from inserttest;
       |    5 |           7
       |    5 |           4
       |    7 |           7
+      |    9 |           7
    10 |   20 |           2
    -1 |    2 |           7
     2 |    3 |          15
    30 |   50 |       10000
-(8 rows)
+(9 rows)
 
 drop table inserttest;
 --
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 66d8633e3e..0a342ca5e9 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index a3a2e383e3..5329d6fd32 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -1780,6 +1780,26 @@ SELECT * FROM y;
  10
 (10 rows)
 
+TRUNCATE TABLE y;
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index bfaa8a3b27..1e4ecb37db 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -7,6 +7,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 
 select * from inserttest;
 
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 23d5778b82..95223bd831 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 46668a903e..5fef9f7542 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -787,6 +787,15 @@ DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
 
 SELECT * FROM y;
 
+TRUNCATE TABLE y;
+
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
 DROP TABLE y;
 
 --
-- 
2.25.1

diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 99811570b7b..5dd211e4563 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -160,6 +160,28 @@ SELECT * FROM itest5;
  16 | iii
 (21 rows)
 
+TRUNCATE TABLE itest5;
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = -1, b = 'aa';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT, b = 'bb';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT;
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET b = 'cc';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = -1, b = 'aaa';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT, b = 'bbb';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT;
+INSERT INTO itest5 OVERRIDING USER VALUE SET b = 'ccc';
+SELECT * FROM itest5;
+ a  |  b  
+----+-----
+ -1 | aa
+ 17 | bb
+ 18 | 
+ 19 | cc
+ 20 | aaa
+ 21 | bbb
+ 22 | 
+ 23 | ccc
+(8 rows)
+
 DROP TABLE itest5;
 INSERT INTO itest3 VALUES (DEFAULT, 'a');
 INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
index 52800f265c2..a659d9e9405 100644
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -77,6 +77,21 @@ INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii');
 
 SELECT * FROM itest5;
+
+TRUNCATE TABLE itest5;
+
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = -1, b = 'aa';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT, b = 'bb';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT;
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET b = 'cc';
+
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = -1, b = 'aaa';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT, b = 'bbb';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT;
+INSERT INTO itest5 OVERRIDING USER VALUE SET b = 'ccc';
+
+SELECT * FROM itest5;
+
 DROP TABLE itest5;
 
 INSERT INTO itest3 VALUES (DEFAULT, 'a');
-- 
2.32.0 (Apple Git-132)

From 757b3013c58cc33bffd509988833588fc0806d6b Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth.palmer3@gmail.com>
Date: Wed, 22 Sep 2021 05:09:28 +0000
Subject: [PATCH 1/2] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values can also be sourced from other tables similar to the INSERT INTO
SELECT FROM syntax:

INSERT INTO t SET c1 = x.c1, c2 = x.c2 FROM x WHERE x.c2 > 10 LIMIT 10;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 56 +++++++++++++-
 src/backend/parser/gram.y                     | 77 ++++++++++++++++++-
 src/test/regress/expected/insert.out          | 13 +++-
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 20 +++++
 src/test/regress/sql/insert.sql               |  1 +
 src/test/regress/sql/insert_conflict.sql      |  3 +
 src/test/regress/sql/with.sql                 |  9 +++
 8 files changed, 173 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 2973b72b815..63c0579d4b7 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -28,6 +28,16 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ 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> ] [, ...] ]
 
+
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
+    SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } } [, ...]
+    [ FROM <replaceable class="parameter">from_clause</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> ]
@@ -263,6 +273,18 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_clause</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+        Refer to the <xref linkend="sql-select"/> statement for a
+        description of the syntax.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -643,6 +665,15 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES
 </programlisting>
   </para>
 
+  <para>
+    Insert a row using <command>SET</command> syntax:
+
+<programlisting>
+INSERT INTO films SET code = 'MH832', title = 'Blade Runner',
+    did = 201, date_prod = DEFAULT, kind = 'SciFi'; 
+</programlisting>
+  </para>
+
   <para>
    This example inserts some rows into table
    <literal>films</literal> from a table <literal>tmp_films</literal>
@@ -689,6 +720,16 @@ WITH upd AS (
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
 </programlisting>
   </para>
+  <para>
+   Insert multiple rows into <literal>employees_log</literal> containing
+the hours worked by each employee from <literal>time_sheets</literal>.
+<programlisting>
+INSERT INTO employees_log SET id = time_sheets.employee,
+    total_hours = sum(time_sheets.hours) FROM time_sheets
+    WHERE time_sheets.date &ge; '2019-11-15' GROUP BY time_sheets.employee;
+</programlisting>
+  </para>
+
   <para>
    Insert or update new distributors as appropriate.  Assumes a unique
    index has been defined that constrains values appearing in the
@@ -745,6 +786,18 @@ INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
     ON CONFLICT (did) WHERE is_active DO NOTHING;
 </programlisting></para>
+  <para>
+   Insert a new film into <literal>watched_films</literal> or increment the
+   number of times seen. Returns the new seen count, example assumes a
+   unique index has been defined that constrains the values appearing in
+   the <literal>title</literal> and <literal>year</literal> columns and 
+   that <literal>seen_count</literal> defaults to 1.
+<programlisting>
+INSERT INTO watched_films SET title = 'Akira', year = 1988
+   ON CONFLICT (title, year) DO UPDATE SET seen_count = watched_films.seen_count + 1
+   RETURNING watched_films.seen_count;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
@@ -755,7 +808,8 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    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>.
+   specify an alternative action with <literal>ON CONFLICT</literal>, and the
+   ability to specify the inserted columns using <literal>SET</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>,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b5966712ce1..0bd9add8d2e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -433,7 +433,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				any_operator expr_list attrs
 				distinct_clause opt_distinct_clause
 				target_list opt_target_list insert_column_list set_target_list
-				set_clause_list set_clause
+				set_clause_list set_clause insert_set_list
 				def_list operator_def_list indirection opt_indirection
 				reloption_list TriggerFuncArgs opclass_item_list opclass_drop_list
 				opclass_purpose opt_opfamily transaction_mode_list_or_empty
@@ -503,7 +503,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 
@@ -540,7 +540,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <range>	extended_relation_expr
 %type <range>	relation_expr_opt_alias
 %type <node>	tablesample_clause opt_repeatable_clause
-%type <target>	target_el set_target insert_column_item
+%type <target>	target_el set_target insert_column_item insert_set_item
 
 %type <str>		generic_option_name
 %type <node>	generic_option_arg
@@ -11197,6 +11197,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -11228,6 +11237,65 @@ insert_column_item:
 				}
 		;
 
+/*
+ * There are two rules here to handle the two different types of INSERT.
+ * INSERT using VALUES and INSERT using SELECT. They can't be combined
+ * because only the VALUES syntax allows specifying DEFAULT.
+ */
+insert_set_clause:
+			SET insert_set_list
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+					ListCell *col_cell;
+					List *values = NIL;
+
+					foreach(col_cell, $2)
+					{
+						ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+
+						values = lappend(values, res_col->val);
+					}
+
+					n->valuesLists = list_make1(values);
+					$$ = makeNode(InsertStmt);
+					$$->cols = $2;
+					$$->selectStmt = (Node *) n;
+				}
+			| SET insert_set_list FROM from_list where_clause group_clause
+			having_clause window_clause opt_sort_clause opt_select_limit
+			opt_for_locking_clause
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+
+					n->targetList = $2;
+					n->fromClause = $4;
+					n->whereClause = $5;
+					n->groupClause = ($6)->list;
+					n->groupDistinct = ($6)->distinct;
+					n->havingClause = $7;
+					n->windowClause = $8;
+					insertSelectOptions(n, $9, $11, $10, NULL, yyscanner);
+					$$ = makeNode(InsertStmt);
+					$$->cols = $2;
+					$$->selectStmt = (Node *) n;
+				}
+		;
+
+insert_set_list:
+			insert_set_item
+				{ $$ = list_make1($1); }
+			| insert_set_list ',' insert_set_item
+				{ $$ = lappend($1, $3); }
+		;
+
+insert_set_item:
+			insert_column_item '=' a_expr
+				{
+					$$ = $1;
+					$$->val = $3;
+				}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
@@ -11617,6 +11685,9 @@ select_clause:
  *
  * NOTE: only the leftmost component SelectStmt should have INTO.
  * However, this is not checked by the grammar; parse analysis must check it.
+ *
+ * NOTE: insert_set_clause also has SELECT-like syntax so if you add any
+ * clauses after from_clause here you may need to add them there as well.
  */
 simple_select:
 			SELECT opt_all_clause opt_target_list
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 5063a3dc221..231542a7cf3 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -9,6 +9,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 select * from inserttest;
  col1 | col2 |  col3   
 ------+------+---------
@@ -16,7 +17,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- insert with similar expression / target_list values (all fail)
@@ -44,7 +46,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- VALUES test
@@ -58,10 +61,11 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
+      |    9 | testing
    10 |   20 | 40
    -1 |    2 | testing
     2 |    3 | values are fun!
-(7 rows)
+(8 rows)
 
 --
 -- TOASTed value test
@@ -74,11 +78,12 @@ select col1, col2, char_length(col3) from inserttest;
       |    5 |           7
       |    5 |           4
       |    7 |           7
+      |    9 |           7
    10 |   20 |           2
    -1 |    2 |           7
     2 |    3 |          15
    30 |   50 |       10000
-(8 rows)
+(9 rows)
 
 drop table inserttest;
 --
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 66d8633e3ec..0a342ca5e92 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index f15ece3bd18..e3a6e34d32a 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -1780,6 +1780,26 @@ SELECT * FROM y;
  10
 (10 rows)
 
+TRUNCATE TABLE y;
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index bfaa8a3b277..1e4ecb37dbe 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -7,6 +7,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 
 select * from inserttest;
 
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 23d5778b821..95223bd8313 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 7ff9de97a5f..6ec7815e02b 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -787,6 +787,15 @@ DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
 
 SELECT * FROM y;
 
+TRUNCATE TABLE y;
+
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
 DROP TABLE y;
 
 --
-- 
2.17.1

From 4c42f9fb4f4b596da9e1fc1b4d15009b6eb035a1 Mon Sep 17 00:00:00 2001
From: wenjing zeng <wjzeng2012@gmail.com>
Date: Fri, 21 Jan 2022 17:24:51 +0800
Subject: [PATCH 2/2] Since this feature adds INSERT OVERRIDING SET syntax, it
 is recommended to add some related testcases.

Jan 21 wenjing zeng    ( 111) Re: [PATCH] Implement INSERT SET syntax
---
 src/test/regress/expected/identity.out | 22 ++++++++++++++++++++++
 src/test/regress/sql/identity.sql      | 15 +++++++++++++++
 2 files changed, 37 insertions(+)

diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 99811570b7b..5dd211e4563 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -160,6 +160,28 @@ SELECT * FROM itest5;
  16 | iii
 (21 rows)
 
+TRUNCATE TABLE itest5;
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = -1, b = 'aa';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT, b = 'bb';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT;
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET b = 'cc';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = -1, b = 'aaa';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT, b = 'bbb';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT;
+INSERT INTO itest5 OVERRIDING USER VALUE SET b = 'ccc';
+SELECT * FROM itest5;
+ a  |  b  
+----+-----
+ -1 | aa
+ 17 | bb
+ 18 | 
+ 19 | cc
+ 20 | aaa
+ 21 | bbb
+ 22 | 
+ 23 | ccc
+(8 rows)
+
 DROP TABLE itest5;
 INSERT INTO itest3 VALUES (DEFAULT, 'a');
 INSERT INTO itest3 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
index 52800f265c2..a659d9e9405 100644
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -77,6 +77,21 @@ INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii');
 
 SELECT * FROM itest5;
+
+TRUNCATE TABLE itest5;
+
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = -1, b = 'aa';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT, b = 'bb';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT;
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET b = 'cc';
+
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = -1, b = 'aaa';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT, b = 'bbb';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT;
+INSERT INTO itest5 OVERRIDING USER VALUE SET b = 'ccc';
+
+SELECT * FROM itest5;
+
 DROP TABLE itest5;
 
 INSERT INTO itest3 VALUES (DEFAULT, 'a');
-- 
2.17.1

From f1ab7edadac846883b9c12f02ecc6c64dd293060 Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth.palmer3@gmail.com>
Date: Thu, 14 Jul 2022 01:47:17 +0000
Subject: [PATCH] Implement INSERT SET syntax

Allow the target column and values of an INSERT statement to be specified
using a SET clause in the same manner as that of an UPDATE statement.

The advantage of using the INSERT SET style is that the columns and values
are kept together, which can make changing or removing a column or value
from a large list easier.

A simple example that uses SET instead of a VALUES() clause:

INSERT INTO t SET c1 = 'foo', c2 = 'bar', c3 = 'baz';

Values can also be sourced from other tables similar to the INSERT INTO
SELECT FROM syntax:

INSERT INTO t SET c1 = x.c1, c2 = x.c2 FROM x WHERE x.c2 > 10 LIMIT 10;

INSERT SET is not part of any SQL standard, however this syntax is also
implemented by MySQL. Their implementation does not support specifying a
FROM clause.
---
 doc/src/sgml/ref/insert.sgml                  | 56 +++++++++++++-
 src/backend/parser/gram.y                     | 77 ++++++++++++++++++-
 src/test/regress/expected/identity.out        | 42 ++++++----
 src/test/regress/expected/insert.out          | 13 +++-
 src/test/regress/expected/insert_conflict.out |  2 +
 src/test/regress/expected/with.out            | 20 +++++
 src/test/regress/sql/identity.sql             |  9 +++
 src/test/regress/sql/insert.sql               |  1 +
 src/test/regress/sql/insert_conflict.sql      |  3 +
 src/test/regress/sql/with.sql                 |  9 +++
 10 files changed, 210 insertions(+), 22 deletions(-)

diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a9af9959c0..b774b6ce74 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -28,6 +28,16 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
     [ 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> ] [, ...] ]
 
+
+[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
+INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ]
+    [ OVERRIDING { SYSTEM | USER} VALUE ]
+    SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } } [, ...]
+    [ FROM <replaceable class="parameter">from_clause</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> ]
@@ -263,6 +273,18 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac
       </listitem>
      </varlistentry>
 
+     <varlistentry>
+      <term><replaceable class="parameter">from_clause</replaceable></term>
+      <listitem>
+       <para>
+        A list of table expressions, allowing columns from other tables
+        to be used as values in the <literal>expression</literal>.
+        Refer to the <xref linkend="sql-select"/> statement for a
+        description of the syntax.
+       </para>
+      </listitem>
+     </varlistentry>
+
      <varlistentry>
       <term><literal>DEFAULT</literal></term>
       <listitem>
@@ -650,6 +672,15 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES
 </programlisting>
   </para>
 
+  <para>
+    Insert a row using <command>SET</command> syntax:
+
+<programlisting>
+INSERT INTO films SET code = 'MH832', title = 'Blade Runner',
+    did = 201, date_prod = DEFAULT, kind = 'SciFi';
+</programlisting>
+  </para>
+
   <para>
    This example inserts some rows into table
    <literal>films</literal> from a table <literal>tmp_films</literal>
@@ -696,6 +727,16 @@ WITH upd AS (
 INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
 </programlisting>
   </para>
+  <para>
+   Insert multiple rows into <literal>employees_log</literal> containing
+the hours worked by each employee from <literal>time_sheets</literal>.
+<programlisting>
+INSERT INTO employees_log SET id = time_sheets.employee,
+    total_hours = sum(time_sheets.hours) FROM time_sheets
+    WHERE time_sheets.date &ge; '2019-11-15' GROUP BY time_sheets.employee;
+</programlisting>
+  </para>
+
   <para>
    Insert or update new distributors as appropriate.  Assumes a unique
    index has been defined that constrains values appearing in the
@@ -752,6 +793,18 @@ INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
     ON CONFLICT (did) WHERE is_active DO NOTHING;
 </programlisting></para>
+  <para>
+   Insert a new film into <literal>watched_films</literal> or increment the
+   number of times seen. Returns the new seen count, example assumes a
+   unique index has been defined that constrains the values appearing in
+   the <literal>title</literal> and <literal>year</literal> columns and
+   that <literal>seen_count</literal> defaults to 1.
+<programlisting>
+INSERT INTO watched_films SET title = 'Akira', year = 1988
+   ON CONFLICT (title, year) DO UPDATE SET seen_count = watched_films.seen_count + 1
+   RETURNING watched_films.seen_count;
+</programlisting>
+  </para>
  </refsect1>
 
  <refsect1>
@@ -762,7 +815,8 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
    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>.
+   specify an alternative action with <literal>ON CONFLICT</literal>, and the
+   ability to specify the inserted columns using <literal>SET</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>,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index c018140afe..a995875449 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -442,7 +442,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 				distinct_clause opt_distinct_clause
 				target_list opt_target_list insert_column_list set_target_list
 				merge_values_clause
-				set_clause_list set_clause
+				set_clause_list set_clause insert_set_list
 				def_list operator_def_list indirection opt_indirection
 				reloption_list TriggerFuncArgs opclass_item_list opclass_drop_list
 				opclass_purpose opt_opfamily transaction_mode_list_or_empty
@@ -512,7 +512,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <list>	OptSeqOptList SeqOptList OptParenthesizedSeqOptList
 %type <defelt>	SeqOptElem
 
-%type <istmt>	insert_rest
+%type <istmt>	insert_rest insert_set_clause
 %type <infer>	opt_conf_expr
 %type <onconflict> opt_on_conflict
 %type <mergewhen>	merge_insert merge_update merge_delete
@@ -553,7 +553,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <range>	extended_relation_expr
 %type <range>	relation_expr_opt_alias
 %type <node>	tablesample_clause opt_repeatable_clause
-%type <target>	target_el set_target insert_column_item
+%type <target>	target_el set_target insert_column_item insert_set_item
 
 %type <str>		generic_option_name
 %type <node>	generic_option_arg
@@ -12063,6 +12063,15 @@ insert_rest:
 					$$->override = $5;
 					$$->selectStmt = $7;
 				}
+			| insert_set_clause
+				{
+					$$ = $1;
+				}
+			| OVERRIDING override_kind VALUE_P insert_set_clause
+				{
+					$$ = $4;
+					$$->override = $2;
+				}
 			| DEFAULT VALUES
 				{
 					$$ = makeNode(InsertStmt);
@@ -12094,6 +12103,65 @@ insert_column_item:
 				}
 		;
 
+/*
+ * There are two rules here to handle the two different types of INSERT.
+ * INSERT using VALUES and INSERT using SELECT. They can't be combined
+ * because only the VALUES syntax allows specifying DEFAULT.
+ */
+insert_set_clause:
+			SET insert_set_list
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+					ListCell *col_cell;
+					List *values = NIL;
+
+					foreach(col_cell, $2)
+					{
+						ResTarget *res_col = (ResTarget *) lfirst(col_cell);
+
+						values = lappend(values, res_col->val);
+					}
+
+					n->valuesLists = list_make1(values);
+					$$ = makeNode(InsertStmt);
+					$$->cols = $2;
+					$$->selectStmt = (Node *) n;
+				}
+			| SET insert_set_list FROM from_list where_clause group_clause
+			having_clause window_clause opt_sort_clause opt_select_limit
+			opt_for_locking_clause
+				{
+					SelectStmt *n = makeNode(SelectStmt);
+
+					n->targetList = $2;
+					n->fromClause = $4;
+					n->whereClause = $5;
+					n->groupClause = ($6)->list;
+					n->groupDistinct = ($6)->distinct;
+					n->havingClause = $7;
+					n->windowClause = $8;
+					insertSelectOptions(n, $9, $11, $10, NULL, yyscanner);
+					$$ = makeNode(InsertStmt);
+					$$->cols = $2;
+					$$->selectStmt = (Node *) n;
+				}
+		;
+
+insert_set_list:
+			insert_set_item
+				{ $$ = list_make1($1); }
+			| insert_set_list ',' insert_set_item
+				{ $$ = lappend($1, $3); }
+		;
+
+insert_set_item:
+			insert_column_item '=' a_expr
+				{
+					$$ = $1;
+					$$->val = $3;
+				}
+		;
+
 opt_on_conflict:
 			ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list	where_clause
 				{
@@ -12646,6 +12714,9 @@ select_clause:
  *
  * NOTE: only the leftmost component SelectStmt should have INTO.
  * However, this is not checked by the grammar; parse analysis must check it.
+ *
+ * NOTE: insert_set_clause also has SELECT-like syntax so if you add any
+ * clauses after from_clause here you may need to add them there as well.
  */
 simple_select:
 			SELECT opt_all_clause opt_target_list
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 5f03d8e14f..08df7aec3a 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -124,41 +124,55 @@ ERROR:  cannot insert a non-DEFAULT value into column "a"
 DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
 HINT:  Use OVERRIDING SYSTEM VALUE to override.
 INSERT INTO itest5 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');  -- ok
+INSERT INTO itest5 SET a = 4, b = 'a'; -- error
+ERROR:  cannot insert a non-DEFAULT value into column "a"
+DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
+HINT:  Use OVERRIDING SYSTEM VALUE to override.
+INSERT INTO itest5 SET a = DEFAULT, b  = 'd'; -- ok
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-1, 'aa');
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-2, 'bb'), (-3, 'cc');
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'dd'), (-4, 'ee');
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-5, 'ff'), (DEFAULT, 'gg');
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'hh'), (DEFAULT, 'ii');
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = -6, b = 'jj';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT, b = 'kk';
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-1, 'aaa');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-2, 'bbb'), (-3, 'ccc');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'ddd'), (-4, 'eee');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii');
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = -6, b = 'jjj';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT, b = 'kkk';
 SELECT * FROM itest5;
  a  |  b  
 ----+-----
   1 | a
   2 | b
   3 | c
+  4 | d
  -1 | aa
  -2 | bb
  -3 | cc
-  4 | dd
+  5 | dd
  -4 | ee
  -5 | ff
-  5 | gg
-  6 | hh
-  7 | ii
-  8 | aaa
-  9 | bbb
- 10 | ccc
- 11 | ddd
- 12 | eee
- 13 | fff
- 14 | ggg
- 15 | hhh
- 16 | iii
-(21 rows)
+  6 | gg
+  7 | hh
+  8 | ii
+ -6 | jj
+  9 | kk
+ 10 | aaa
+ 11 | bbb
+ 12 | ccc
+ 13 | ddd
+ 14 | eee
+ 15 | fff
+ 16 | ggg
+ 17 | hhh
+ 18 | iii
+ 19 | jjj
+ 20 | kkk
+(26 rows)
 
 DROP TABLE itest5;
 INSERT INTO itest3 VALUES (DEFAULT, 'a');
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index dd4354fc7d..3dd6ef855c 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -9,6 +9,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 select * from inserttest;
  col1 | col2 |  col3   
 ------+------+---------
@@ -16,7 +17,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- insert with similar expression / target_list values (all fail)
@@ -44,7 +46,8 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
-(4 rows)
+      |    9 | testing
+(5 rows)
 
 --
 -- VALUES test
@@ -58,10 +61,11 @@ select * from inserttest;
       |    5 | testing
       |    5 | test
       |    7 | testing
+      |    9 | testing
    10 |   20 | 40
    -1 |    2 | testing
     2 |    3 | values are fun!
-(7 rows)
+(8 rows)
 
 --
 -- TOASTed value test
@@ -74,11 +78,12 @@ select col1, col2, char_length(col3) from inserttest;
       |    5 |           7
       |    5 |           4
       |    7 |           7
+      |    9 |           7
    10 |   20 |           2
    -1 |    2 |           7
     2 |    3 |          15
    30 |   50 |       10000
-(8 rows)
+(9 rows)
 
 drop table inserttest;
 --
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 66d8633e3e..0a342ca5e9 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -236,6 +236,8 @@ insert into insertconflicttest values (2, 'Orange') on conflict (key, key, key)
 insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 30dd900e11..9a10453b67 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -1790,6 +1790,26 @@ SELECT * FROM y;
  10
 (10 rows)
 
+TRUNCATE TABLE y;
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+SELECT * FROM y;
+ a  
+----
+ 21
+ 22
+ 23
+ 24
+ 25
+ 26
+ 27
+ 28
+ 29
+ 30
+(10 rows)
+
 DROP TABLE y;
 --
 -- error cases
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
index 9b8db2e4a3..f844d7ebfa 100644
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -64,18 +64,27 @@ INSERT INTO itest5 VALUES (DEFAULT, 'b'), (3, 'c');  -- error
 INSERT INTO itest5 VALUES (2, 'b'), (DEFAULT, 'c');  -- error
 INSERT INTO itest5 VALUES (DEFAULT, 'b'), (DEFAULT, 'c');  -- ok
 
+INSERT INTO itest5 SET a = 4, b = 'a'; -- error
+INSERT INTO itest5 SET a = DEFAULT, b  = 'd'; -- ok
+
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-1, 'aa');
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-2, 'bb'), (-3, 'cc');
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'dd'), (-4, 'ee');
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (-5, 'ff'), (DEFAULT, 'gg');
 INSERT INTO itest5 OVERRIDING SYSTEM VALUE VALUES (DEFAULT, 'hh'), (DEFAULT, 'ii');
 
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = -6, b = 'jj';
+INSERT INTO itest5 OVERRIDING SYSTEM VALUE SET a = DEFAULT, b = 'kk';
+
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-1, 'aaa');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-2, 'bbb'), (-3, 'ccc');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'ddd'), (-4, 'eee');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (-5, 'fff'), (DEFAULT, 'ggg');
 INSERT INTO itest5 OVERRIDING USER VALUE VALUES (DEFAULT, 'hhh'), (DEFAULT, 'iii');
 
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = -6, b = 'jjj';
+INSERT INTO itest5 OVERRIDING USER VALUE SET a = DEFAULT, b = 'kkk';
+
 SELECT * FROM itest5;
 DROP TABLE itest5;
 
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index bdcffd0314..8265c8d993 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -7,6 +7,7 @@ insert into inserttest (col2, col3) values (3, DEFAULT);
 insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT);
 insert into inserttest values (DEFAULT, 5, 'test');
 insert into inserttest values (DEFAULT, 7);
+insert into inserttest set col1 = DEFAULT, col2 = 9;
 
 select * from inserttest;
 
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 23d5778b82..95223bd831 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -97,6 +97,9 @@ insert into insertconflicttest
 values (1, 'Apple'), (2, 'Orange')
 on conflict (key) do update set (fruit, key) = (excluded.fruit, excluded.key);
 
+-- Using insert set syntax
+insert into insertconflicttest set key = 1, fruit = 'Banana' on conflict (key) do update set fruit = excluded.fruit;
+
 -- Give good diagnostic message when EXCLUDED.* spuriously referenced from
 -- RETURNING:
 insert into insertconflicttest values (1, 'Apple') on conflict (key) do update set fruit = excluded.fruit RETURNING excluded.fruit;
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index 5c52561a8a..b8c68a3e29 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -797,6 +797,15 @@ DELETE FROM y USING t WHERE t.a = y.a RETURNING y.a;
 
 SELECT * FROM y;
 
+TRUNCATE TABLE y;
+
+WITH t AS (
+    SELECT generate_series(1, 10) AS a
+)
+INSERT INTO y SET a = t.a+20 FROM t;
+
+SELECT * FROM y;
+
 DROP TABLE y;
 
 --
-- 
2.25.1