Hi,
Here's a patch for $SUBJECT. I'll probably work on the docs a bit more
before the next CF, but I thought I'd post it anyway.
.m
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index e710cf4..33e577b 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -22,12 +22,21 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable>
[, ...] ]
-INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS
<replaceable class="parameter">alias</replaceable> ] [ ( <replaceable
class="PARAMETER">column_name</replaceable> [, ...] ) ]
- { DEFAULT VALUES | VALUES ( { <replaceable
class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] |
<replaceable class="PARAMETER">query</replaceable> }
+INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS
<replaceable class="parameter">alias</replaceable> ]
+ {
+ [ column_list ] VALUES ( { <replaceable
class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] |
+ [ column_list ] <replaceable class="PARAMETER">query</replaceable> |
+ DEFAULT VALUES |
+ SET <replaceable class="PARAMETER">column_name</replaceable> = {
<replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...]
+ }
[ ON CONFLICT [ <replaceable
class="parameter">conflict_target</replaceable> ] <replaceable
class="parameter">conflict_action</replaceable> ]
[ RETURNING * | <replaceable
class="parameter">output_expression</replaceable> [ [ AS ] <replaceable
class="parameter">output_name</replaceable> ] [, ...] ]
-<phrase>where <replaceable class="parameter">conflict_target</replaceable> can
be one of:</phrase>
+<phrase>where <replaceable class="parameter">column_list</replaceable>
is:</phrase>
+
+ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] )
+
+<phrase>and <replaceable class="parameter">conflict_target</replaceable> can
be one of:</phrase>
( { <replaceable class="parameter">index_column_name</replaceable> | (
<replaceable class="parameter">index_expression</replaceable> ) } [ COLLATE
<replaceable class="parameter">collation</replaceable> ] [ <replaceable
class="parameter">opclass</replaceable> ] [, ...] ) [ WHERE <replaceable
class="PARAMETER">index_predicate</replaceable> ]
ON CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
@@ -53,13 +62,26 @@ INSERT INTO <replaceable
class="PARAMETER">table_name</replaceable> [ AS <replac
</para>
<para>
- The target column names can be listed in any order. If no list of
- column names is given at all, the default is all the columns of the
- table in their declared order; or the first <replaceable>N</> column
- names, if there are only <replaceable>N</> columns supplied by the
- <literal>VALUES</> clause or <replaceable>query</>. The values
- supplied by the <literal>VALUES</> clause or <replaceable>query</> are
- associated with the explicit or implicit column list left-to-right.
+ The target column names in a <replaceable>column_list</> can be
+ listed in any order. If no <replaceable>column_list</> is given at
+ all (and the <literal>SET</> syntax is not used), the default is all
+ the columns of the table in their declared order; or the first
+ <replaceable>N</> column names, if there are only <replaceable>N</>
+ columns supplied by the <literal>VALUES</> clause or
+ <replaceable>query</>. The values supplied by the <literal>VALUES</>
+ clause or <replaceable>query</> are associated with the explicit or
+ implicit column list left-to-right.
+ </para>
+
+ <para>
+ Instead of a <replaceable>column_list</> and a <literal>VALUES</>
+ clause, a <literal>SET</> clause similar to that of an
+ <command>UPDATE</command> can be used instead. The advantage of the
+ <literal>SET</> clause is that instead of matching the elements in
+ the two lists by ordinal position, the column name and the
+ expression to assign to that column are visually next to each other.
+ This can make long column assignment lists significantly more
+ readable.
</para>
<para>
@@ -691,13 +713,13 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad
International')
<para>
<command>INSERT</command> conforms to the SQL standard, except that
the <literal>RETURNING</> clause is a
- <productname>PostgreSQL</productname> extension, as is the ability
- to use <literal>WITH</> with <command>INSERT</>, and the ability to
- specify an alternative action with <literal>ON CONFLICT</>.
- Also, the case in
- which a column name list is omitted, but not all the columns are
- filled from the <literal>VALUES</> clause or <replaceable>query</>,
- is disallowed by the standard.
+ <productname>PostgreSQL</productname> extension, as is the
+ <literal>SET</> clause when used instead of a VALUES clause, the
+ ability to use <literal>WITH</> with <command>INSERT</>, and the
+ ability to specify an alternative action with <literal>ON
+ CONFLICT</>. Also, the case in which a column name list is omitted,
+ but not all the columns are filled from the <literal>VALUES</> clause
+ or <replaceable>query</>, is disallowed by the standard.
</para>
<para>
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 29c8c4e..55c4cb3 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -466,8 +466,9 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
stmt->onConflictClause->action
== ONCONFLICT_UPDATE);
/*
- * We have three cases to deal with: DEFAULT VALUES (selectStmt ==
NULL),
- * VALUES list, or general SELECT input. We special-case VALUES, both
for
+ * We have four cases to deal with: DEFAULT VALUES (selectStmt == NULL
and
+ * cols == NIL), SET syntax (selectStmt == NULL but cols != NIL), VALUES
+ * list, or general SELECT input. We special-case VALUES, both for
* efficiency and so we can handle DEFAULT specifications.
*
* The grammar allows attaching ORDER BY, LIMIT, FOR UPDATE, or WITH to
a
@@ -522,7 +523,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
/*
* Determine which variant of INSERT we have.
*/
- if (selectStmt == NULL)
+ if (selectStmt == NULL && stmt->cols == NIL)
{
/*
* We have INSERT ... DEFAULT VALUES. We can handle this case
by
@@ -531,6 +532,25 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
*/
exprList = NIL;
}
+ else if (selectStmt == NULL)
+ {
+ /*
+ * INSERT INTO ... SET syntax.
+ */
+ Assert(stmt->cols != NIL);
+
+ stmt->cols = transformUpdateTargetList(pstate, stmt->cols);
+
+ exprList = NIL;
+ foreach(lc, stmt->cols)
+ {
+ TargetEntry *rt = (TargetEntry *) lfirst(lc);
+
+ Assert(IsA(rt, TargetEntry));
+
+ exprList = lappend(exprList, rt->expr);
+ }
+ }
else if (isGeneralSelect)
{
/*
@@ -2130,7 +2150,8 @@ transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt)
/*
* transformUpdateTargetList -
- * handle SET clause in UPDATE/INSERT ... ON CONFLICT UPDATE
+ * handle SET clause in UPDATE / INSERT ... ON CONFLICT UPDATE / INSERT
INTO
+ * ... SET
*/
static List *
transformUpdateTargetList(ParseState *pstate, List *origTlist)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index edf4516..8062ee3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -360,7 +360,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 multiple_set_clause
+ insert_set_clause_list set_clause_list
set_clause multiple_set_clause
ctext_expr_list ctext_row def_list
operator_def_list indirection opt_indirection
reloption_list group_clause TriggerFuncArgs
select_limit
opt_select_limit opclass_item_list
opclass_drop_list
@@ -9659,6 +9659,12 @@ insert_rest:
$$->cols = $2;
$$->selectStmt = $4;
}
+ | SET insert_set_clause_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = $2;
+ $$->selectStmt = NULL;
+ }
| DEFAULT VALUES
{
$$ = makeNode(InsertStmt);
@@ -9685,6 +9691,17 @@ insert_column_item:
}
;
+/*
+ * This is different from set_clause_list used in UPDATE because the SelectStmt
+ * syntax already does everything you might want to do in an in INSERT.
+ */
+insert_set_clause_list:
+ single_set_clause
+ { $$ = list_make1($1); }
+ | insert_set_clause_list ',' single_set_clause
+ { $$ = lappend($1,$3); }
+ ;
+
opt_on_conflict:
ON CONFLICT opt_conf_expr DO UPDATE SET set_clause_list
where_clause
{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d36d9c6..380a4d4 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -405,9 +405,11 @@ typedef struct A_ArrayExpr
* 'AS ColumnLabel' clause, or NULL if there was none, and 'val' is the
* value expression itself. The 'indirection' field is not used.
*
- * INSERT uses ResTarget in its target-column-names list. Here, 'name' is
- * the name of the destination column, 'indirection' stores any subscripts
- * attached to the destination, and 'val' is not used.
+ * INSERT uses ResTarget in its "cols" list. Here, 'name' is the name of the
+ * destination column, 'indirection' stores any subscripts attached to the
+ * destination. 'val' is only used if "cols" is from an INSERT ... SET syntax
+ * statement; otherwise "cols" only represents the column name list, and thus
+ * 'val' is unused.
*
* In an UPDATE target list, 'name' is the name of the destination column,
* 'indirection' stores any subscripts attached to the destination, and
diff --git a/src/test/regress/expected/insert.out
b/src/test/regress/expected/insert.out
index 96c7f9e..eaec247 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -80,4 +80,97 @@ select col1, col2, char_length(col3) from inserttest;
30 | 50 | 10000
(8 rows)
+--
+-- insert with SET syntax
+--
+truncate inserttest;
+create type inserttestcomplextype as (f1 int, f2 int);
+alter table inserttest add column col4 int[];
+alter table inserttest add column col5 inserttestcomplextype;
+-- these all fail
+insert into inserttest set
+ colnonexistent = 1;
+ERROR: column "colnonexistent" of relation "inserttest" does not exist
+LINE 2: colnonexistent = 1;
+ ^
+insert into inserttest set
+ col1 = col1;
+ERROR: column "col1" does not exist
+LINE 2: col1 = col1;
+ ^
+HINT: There is a column named "col1" in table "inserttest", but it cannot be
referenced from this part of the query.
+insert into inserttest set
+ col4[1] = 1,
+ col4 = '{}';
+ERROR: column "col4" specified more than once
+LINE 3: col4 = '{}';
+ ^
+insert into inserttest set
+ col5.nonexistent = 1;
+ERROR: cannot assign to field "nonexistent" of column "col5" because there is
no such column in data type inserttestcomplextype
+LINE 2: col5.nonexistent = 1;
+ ^
+insert into inserttest set
+ col5.f1 = 1,
+ col5 = '()';
+ERROR: column "col5" specified more than once
+LINE 3: col5 = '()';
+ ^
+insert into inserttest set
+ col1 = 1,
+ col1 = 1;
+ERROR: column "col1" specified more than once
+LINE 3: col1 = 1;
+ ^
+select * from inserttest;
+ col1 | col2 | col3 | col4 | col5
+------+------+------+------+------
+(0 rows)
+
+truncate table inserttest;
+-- defaults
+insert into inserttest set
+ col1 = default,
+ col2 = default,
+ col3 = default;
+ERROR: null value in column "col2" violates not-null constraint
+DETAIL: Failing row contains (null, null, testing, null, null).
+insert into inserttest set
+ col2 = 3,
+ col3 = default;
+insert into inserttest set
+ col1 = default,
+ col2 = 5,
+ col3 = default;
+select * from inserttest;
+ col1 | col2 | col3 | col4 | col5
+------+------+---------+------+------
+ | 3 | testing | |
+ | 5 | testing | |
+(2 rows)
+
+truncate table inserttest;
+-- more complex cases
+insert into inserttest set
+ col2 = (select count(*))::int;
+insert into inserttest set
+ col2 = 1,
+ col4[1] = 1,
+ -- weird, but accepted
+ col4[1] = 2;
+insert into inserttest set
+ col2 = 1,
+ col5.f1 = 1,
+ -- weird, but accepted
+ col5.f1 = 2;
+select * from inserttest;
+ col1 | col2 | col3 | col4 | col5
+------+------+---------+------+------
+ | 1 | testing | |
+ | 1 | testing | {2} |
+ | 1 | testing | | (2,)
+(3 rows)
+
+truncate table inserttest;
drop table inserttest;
+drop type inserttestcomplextype;
diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql
index a0ae850..652d4a5 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -35,4 +35,67 @@ insert into inserttest values(30, 50, repeat('x', 10000));
select col1, col2, char_length(col3) from inserttest;
+--
+-- insert with SET syntax
+--
+truncate inserttest;
+
+create type inserttestcomplextype as (f1 int, f2 int);
+alter table inserttest add column col4 int[];
+alter table inserttest add column col5 inserttestcomplextype;
+
+-- these all fail
+insert into inserttest set
+ colnonexistent = 1;
+insert into inserttest set
+ col1 = col1;
+insert into inserttest set
+ col4[1] = 1,
+ col4 = '{}';
+insert into inserttest set
+ col5.nonexistent = 1;
+insert into inserttest set
+ col5.f1 = 1,
+ col5 = '()';
+insert into inserttest set
+ col1 = 1,
+ col1 = 1;
+
+select * from inserttest;
+truncate table inserttest;
+
+-- defaults
+insert into inserttest set
+ col1 = default,
+ col2 = default,
+ col3 = default;
+insert into inserttest set
+ col2 = 3,
+ col3 = default;
+insert into inserttest set
+ col1 = default,
+ col2 = 5,
+ col3 = default;
+
+select * from inserttest;
+truncate table inserttest;
+
+-- more complex cases
+insert into inserttest set
+ col2 = (select count(*))::int;
+insert into inserttest set
+ col2 = 1,
+ col4[1] = 1,
+ -- weird, but accepted
+ col4[1] = 2;
+insert into inserttest set
+ col2 = 1,
+ col5.f1 = 1,
+ -- weird, but accepted
+ col5.f1 = 2;
+
+select * from inserttest;
+truncate table inserttest;
+
drop table inserttest;
+drop type inserttestcomplextype;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
