Re: [HACKERS] INSERT .. SET syntax
On 09/05/2016 03:58 PM, Simon Riggs wrote: > On 3 July 2016 at 20:36, Marko Tiikkaja wrote: > >> 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. > > I think this should be Returned With Feedback. You're probably right (even though you're quoting the wrong message), so I've changed it to that. Marko, please resubmit an updated patch. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT .. SET syntax
On 3 July 2016 at 20:36, Marko Tiikkaja wrote: > 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. I think this should be Returned With Feedback. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT .. SET syntax
On 08/31/2016 04:12 PM, Marko Tiikkaja wrote: > Hello hello, > > Here's a rebased and updated patch for $SUBJECT for the September commit > fest. Review: This patch is pretty straightforward, using mostly already existing infrastructure. I tried to break it in various ways and could not. I do have a few comments, though. In insert.sgml, some things are and some are . I don't expect this patch to fix those inconsistencies, but it should certainly not perpetuate them. This code comment in gram.y took me a while to figure out: +/* + * 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. + */ If the SelectStmt is all we need, why is this patch here? I would prefer wording such as "This is different from set_clause_list used in UPDATE because we don't want multiple_set_clause. The INSERT INTO ... SELECT variant may be more appropriate in such cases." Or something. Aside from those trivialities, the main question about this patch is if we actually want it. It is not standard SQL syntax, and the only other product I've located that uses it (or anything like it) is MySQL. However, I can see how it would be a huge win for very wide tables and so my personal opinion is to accept this syntax as a PostgreSQL extension to the standard. Marking ready for committer, the minor gripes above notwithstanding. -- Vik Fearing +33 6 46 75 15 36 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT .. SET syntax
Hello hello,
Here's a rebased and updated patch for $SUBJECT for the September commit
fest.
.m
*** a/doc/src/sgml/ref/insert.sgml
--- b/doc/src/sgml/ref/insert.sgml
***
*** 22,33 PostgreSQL documentation
[ WITH [ RECURSIVE ] with_query [, ...] ]
! INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
! { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
! where conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
--- 22,42
[ WITH [ RECURSIVE ] with_query [, ...] ]
! INSERT INTO table_name [ AS alias ]
! {
! [ column_list ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] |
! [ column_list ] query |
! DEFAULT VALUES |
! SET column_name = { expression | DEFAULT } [, ...]
! }
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
! where column_list is:
!
! ( column_name [, ...] )
!
! and conflict_target can be one of:
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
***
*** 53,65 INSERT INTO table_name [ AS
!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 N column
!names, if there are only N columns supplied by the
!VALUES clause or query. The values
!supplied by the VALUES clause or query are
!associated with the explicit or implicit column list left-to-right.
--- 62,87
!The target column names in a column_list can be
!listed in any order. If no column_list is given at
!all (and the SET syntax is not used), the default is all
!the columns of the table in their declared order; or the first
!N column names, if there are only N
!columns supplied by the VALUES clause or
!query. The values supplied by the VALUES
!clause or query are associated with the explicit or
!implicit column list left-to-right.
!
!
!
! Instead of a column_list and a VALUES
! clause, a SET clause similar to that of an
! UPDATE can be used instead. The advantage of the
! 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.
***
*** 690,702 INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
INSERT conforms to the SQL standard, except that
the RETURNING clause is a
!PostgreSQL extension, as is the ability
!to use WITH with INSERT, and the ability to
!specify an alternative action with ON CONFLICT.
!Also, the case in
!which a column name list is omitted, but not all the columns are
!filled from the VALUES clause or query,
!is disallowed by the standard.
--- 712,724
INSERT conforms to the SQL standard, except that
the RETURNING clause is a
!PostgreSQL extension, as is the
!SET clause when used instead of a VALUES clause, the
!ability to use WITH with INSERT, and the
!ability to specify an alternative action with ON
!CONFLICT. Also, the case in which a column name list is omitted,
!but not all the columns are filled from the VALUES clause
!or query, is disallowed by the standard.
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***
*** 467,474 transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
stmt->onConflictClause->action == ONCONFLICT_UPDATE);
/*
! * We have three cases to deal with: DEFAULT VALUES (selectStmt == NULL),
! * VALUES list, or general SELECT input. We special-case VALUES, both for
* efficiency and so we can handle DEFAULT specifications.
*
* The grammar allows attaching ORDER BY, LIMIT, FOR UPDATE, or WITH to a
--- 467,475
stmt->onConflictClause->action == ONCONFLICT_UPDATE);
/*
! * We have four cases to deal with: DEFAULT VALUES (selectStmt == NULL and
! * cols == NIL), SET syntax (selectStmt == NULL but cols != NIL), VALUES
! * list, or general SELECT input. We special-case VALUES, both for
* efficiency and so we can handle DEFAULT specifications.
*
* The grammar allows attaching ORDER BY, LIMIT, FOR UPDATE, or WITH to a
***
*** 523,529 transfo
Re: [HACKERS] INSERT .. SET syntax
On Mon, Jul 4, 2016 at 1:06 AM, Marko Tiikkaja wrote: > 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. > I could see that it can be useful in certain cases as described in the documentation part of the patch. I noticed that you have used transformUpdateTargetList() to generate expression list for this case, but that function raises some internal errors which indicates that error is from Update. I think that might be misleading to users, if they ever got raised. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] INSERT .. SET syntax
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
[ WITH [ RECURSIVE ] with_query
[, ...] ]
-INSERT INTO table_name [ AS
alias ] [ ( column_name [, ...] ) ]
-{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] |
query }
+INSERT INTO table_name [ AS
alias ]
+{
+[ column_list ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] |
+[ column_list ] query |
+DEFAULT VALUES |
+SET column_name = {
expression | DEFAULT } [, ...]
+}
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
-where conflict_target can
be one of:
+where column_list
is:
+
+( column_name [, ...] )
+
+and conflict_target can
be one of:
( { index_column_name | (
index_expression ) } [ COLLATE
collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
@@ -53,13 +62,26 @@ INSERT INTO table_name [ AS
- 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 N column
- names, if there are only N columns supplied by the
- VALUES clause or query. The values
- supplied by the VALUES clause or query are
- associated with the explicit or implicit column list left-to-right.
+ The target column names in a column_list can be
+ listed in any order. If no column_list is given at
+ all (and the SET syntax is not used), the default is all
+ the columns of the table in their declared order; or the first
+ N column names, if there are only N
+ columns supplied by the VALUES clause or
+ query. The values supplied by the VALUES
+ clause or query are associated with the explicit or
+ implicit column list left-to-right.
+
+
+
+Instead of a column_list and a VALUES
+clause, a SET clause similar to that of an
+UPDATE can be used instead. The advantage of the
+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.
@@ -691,13 +713,13 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad
International')
INSERT conforms to the SQL standard, except that
the RETURNING clause is a
- PostgreSQL extension, as is the ability
- to use WITH with INSERT, and the ability to
- specify an alternative action with ON CONFLICT.
- Also, the case in
- which a column name list is omitted, but not all the columns are
- filled from the VALUES clause or query,
- is disallowed by the standard.
+ PostgreSQL extension, as is the
+ SET clause when used instead of a VALUES clause, the
+ ability to use WITH with INSERT, and the
+ ability to specify an alternative action with ON
+ CONFLICT. Also, the case in which a column name list is omitted,
+ but not all the columns are filled from the VALUES clause
+ or query, is disallowed by the standard.
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)
+ {
+ /*
+
