Hello Rachel,
On Wed, 22 Sept 2021 at 17:13, Rachel Heaton <rachelmhea...@gmail.com> wrote:
>
> > On 4/23/20 8:04 PM, Gareth Palmer wrote:
> > >
> > > Thank you for the review, attached is v7 of the patch which should
> > > apply correcly to HEAD.
> > >
>
> Hello Gareth,
>
> This patch no longer applies to HEAD, can you please submit a rebased version?
Attached is a rebased version that should apply to HEAD.
Gareth
> Thanks,
> Rachel
>
>
>
>
From 7e222b4068e445a723f1692c5cdeec99d498a161 Mon Sep 17 00:00:00 2001
From: Gareth Palmer <gareth.palm...@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 ≥ '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
