Hi, Thanks for the review.
When the same array element or field target was assigned twice in one row, transformInsertSetClause() collapsed the two into one slot and kept only the last value. The earlier expression was never analysed, so an invalid reference (or a nextval() call) was silently dropped. I've followed your suggestion. The same-row duplicate check now also compares the indirection with equal(), so an identical repeated target is rejected: insert into ta set id=1, arr[0]=0, arr[1]=some_random, arr[1]=2; ERROR: column "arr" specified more than once This applies to the single-row form, the parenthesised single-row form, and every row of a multi-row INSERT ... SET. Assignments to different elements or fields of the same column (arr[1]=1, arr[2]=2) are still allowed, and so are whole-column assignments that differ between rows. I've added regression tests for these cases. -- Thanks & Regards, Suraj kharage, enterprisedb.com <https://www.enterprisedb.com/> On Tue, Oct 20, 2026 at 2:54 AM Mario González <[email protected]> wrote: > On Mon, 12 Oct 2026 at 09:02, Vaibhav Dalvi < > [email protected]> wrote: > >> Hi Suraj, >> >> This makes sense to me. >> >> Regards, >> Vaibhav >> >> > Hello Suraj, I was having another look and I ran into this scenario that I > think it's an error worth fixing. Not sure if you've fixed this in a future > version already? > > Whilst testing v6 I came across a corner case with repeated array/field > targets. If the same target is assigned twice in one row, the earlier > expression is quietly discarded before it's ever analysed. > > A single invalid assignment is caught, as you'd expect: > > create table ta (id int, arr int[]); > > insert into ta set id=1, arr[0]=0, arr[1]=some_random; > ERROR: column "some_random" does not exist > > But repeating the target lets it through: > > insert into ta set id=1, arr[0]=0, arr[1]=some_random, arr[1]=2; > INSERT 0 1 > > The column-list form, insert into ta (id, arr[0], arr[1], arr[1]) values > (...), rejects the same thing, and UPDATE ... SET evaluates both > expressions, so any side effects (e.g. nextval()) are lost here too. The > comment in transformInsertSetClause() saying this matches column-list > INSERT/UPDATE behaviour isn't quite right. > > Hardly a serious issue, but I'd suggest simply rejecting an identical > repeated target within a row, by adding equal(res->indirection, > row_col->indirection) to the first-pass duplicate check, and adding a > regression test for it. > > Also, I reckon there are other smaller issues with arrays but before going > any further, the main problem IMHO, it seems there's no committer > sponsoring this? I've leanrt that the code could be rock solid but it can > stay as-is forever, or worse, someone else with commit perms can push a > similar fix bypassing all this effort. > > Best. > Mario. > > >> >> >> On Thu, Sep 10, 2026 at 2:58 PM Suraj Kharage < >> [email protected]> wrote: >> >>> >>> On Wed, Sep 2, 2026 at 6:10 PM Vaibhav Dalvi < >>> [email protected]> wrote: >>> >>>> Hi, >>>> >>>> Thanks Suraj for the clarification. I see two issues here, both >>>> stemming from the same root cause: >>>> >>>> *Issue #1: A row that skips an array column entirely gets wrongly >>>> rejected* >>>> Setup: >>>> create table ta(id int primary key, arr int[] default array[1]); >>>> >>>> Command: >>>> insert into ta set (id=70, arr[1]=1), (id=71, arr[1]=2), (id=72); >>>> >>>> Actual result: >>>> ERROR: cannot set an array element to DEFAULT >>>> >>>> What's wrong: row id=72 doesn't mention arr in any way, no slot, no >>>> whole column, nothing. >>>> In plain English, it's saying "I don't care about arr, just use >>>> whatever it normally is." >>>> That should give it the column's default ({1} here, since one was >>>> declared, or NULL if not) >>>> exactly like skipping any other ordinary column works fine. Instead, >>>> because other rows in the >>>> same statement touch arr[1], the code silently treats row 72's silence >>>> as if it had explicitly written >>>> arr[1] = DEFAULT, and setting a single array slot to DEFAULT is always >>>> that asked for nothing >>>> gets blamed for a request it never made, and the whole statement fails. >>>> >>>> Expected result: >>>> id | arr >>>> ----+----- >>>> 70 | {1} >>>> 71 | {2} >>>> 72 | {1} -- the column's default, same as ny other statement >>>> >>>> *Issue #2: Mixing "whole column" and "one slot" across different rows >>>> gets wrongly rejected* >>>> Setup: >>>> create table t1(id int primary key, arr int[] default array[1]); >>>> >>>> Command: >>>> insert into t1 set (id=1, arr[1]=1), (id=2, arr=default); >>>> >>>> Actual result: >>>> ERROR: column "arr" specified more than once >>>> >>>> What's wrong: Row 1 wants to set slot 1 of its own array. Row 2 wants >>>> its own array to just be the default. >>>> These are two completely separate rows with two completely separate >>>> array values, nothing about them >>>> actually conflicts. However, the code doesn't check rows independently; >>>> it first lumps every row's arr-related >>>> target into one shared bucket, and only then checks for conflicts. In >>>> that shared bucket, "the whole column" >>>> and "just slot 1" look like a contradiction, even though they aren't, >>>> since they belong to two different rows, not >>>> one row saying two contradictory things about the same value. >>>> >>>> Expected result: >>>> id | arr >>>> ----+----- >>>> 1 | {1} >>>> 2 | {1} -- the column's default >>>> >>>> Another thought here is: should we restrict array/field indirection >>>> targets instead like below? >>>> >>> Thanks for looking into this. >>> >>> Both issues share the same root cause: multi-row SET merges all rows >>> into one shared column list, >>> so once a column uses arr[1]-style indirection, every row must supply >>> that exact same target. >>> I think if we make this work, then need bypassing core Postgres >>> INSERT/rewrite internals logic and it becomes complex. >>> Postgres builds one assignment expression per column and reuses it for >>> every row (via Vars into the VALUES RTE), >>> and the rewriter refuses to merge a whole-column value with an >>> element/field assignment on the same column >>> ("multiple assignments to same column" in process_matched_tle()). >>> >>> So I went with your suggested restriction, but narrower: if all rows set >>> the same array/field target (e.g. all rows set arr[1]), >>> it keeps working as before. But if rows disagree — one row skips it, or >>> sets the whole column, or targets a different element/field — >>> we now raise one clear error. >>> Updated docs and tests accordingly. >>> >>> Please let me know your thoughts on the same. >>> >>> Regards, >>> Suraj >>> >>> >>> >>>> insert into t1 set (id=1, arr[1]=1), (id=2, arr=default); >>>> ERROR: array/field indirection targets are not supported in multi-row >>>> INSERT ... SET >>>> >>>> Per my knowledge, MySQL and MariaDB support this INSERT ... SET syntax: >>>> - Standard MySQL does not support multi-row INSERT ... SET directly >>>> using SET ... SET. >>>> In MySQL, INSERT ... SET is strictly single-row: >>>> >>>> INSERT INTO employees SET id = 1, name = 'Alice', salary = 70000; >>>> >>>> - MariaDB extends the standard INSERT ... SET syntax to support >>>> multiple rows by repeating the SET clause for each row: >>>> >>>> INSERT INTO employees >>>> SET id = 1, name = 'Alice', salary = 70000, >>>> SET id = 2, name = 'Bob', salary = 80000, >>>> SET id = 3, name = 'Charlie', salary = 75000; >>>> >>>> The point is to check if we can think in that direction? >>>> >>>> Regards, >>>> Vaibhav >>>> >>>> On Mon, Aug 31, 2026 at 10:20 AM Suraj Kharage < >>>> [email protected]> wrote: >>>> >>>>> >>>>> On Fri, Aug 28, 2026 at 11:39 AM Vaibhav Dalvi < >>>>> [email protected]> wrote: >>>>> >>>>>> create table ta(id int primary key, arr int[] default array[1]); >>>>>> >>>>>> -- classic VALUES: row 3 gets the table's default {1} >>>>>> insert into ta values (70, array[1]), (71, array[2]), (72, default); >>>>>> -- id | arr >>>>>> -- ----+----- >>>>>> -- 70 | {1} >>>>>> -- 71 | {2} >>>>>> -- 72 | {1} >>>>>> >>>>>> -- new SET syntax: same intent, row 3 just omits arr >>>>>> insert into ta set (id=170, arr[1]=1), (id=171, arr[1]=2), (id=172); >>>>>> -- ERROR: cannot set an array element to DEFAULT >>>>>> >>>>> >>>>> Thanks for reporting this. I have looked into this and feel that this >>>>> is an existing behavior for INSERT command. >>>>> transformAssignedExpr() unconditionally rejects DEFAULT for any >>>>> indirection target (array element or subfield). >>>>> >>>>> For e.g. for plain insert as well it will fail - >>>>> postgres@2495950=#INSERT INTO ta(id, arr[1]) VALUES >>>>> (70,1),(71,2),(72, default); >>>>> ERROR: cannot set an array element to DEFAULT >>>>> LINE 1: INSERT INTO ta(id, arr[1]) VALUES (70,1),(71,2),(72, default... >>>>> >>>>> >>>>> It will work in both cases, if we try to insert in arr column. >>>>> postgres@2495950=#INSERT INTO ta VALUES >>>>> (70,array[1]),(71,array[2]),(72, default); >>>>> INSERT 0 3 >>>>> postgres@2495950=#select * from ta; >>>>> id | arr >>>>> ----+----- >>>>> 70 | {1} >>>>> 71 | {2} >>>>> 72 | {1} >>>>> (3 rows) >>>>> >>>>> >>>>> postgres@2495950=#insert into ta set (id=170, arr=array[1]), (id=171, >>>>> arr=array[2]), (id=172, arr=default); >>>>> INSERT 0 3 >>>>> postgres@2495950=#select * from ta; >>>>> id | arr >>>>> -----+----- >>>>> 170 | {1} >>>>> 171 | {2} >>>>> 172 | {1} >>>>> (3 rows) >>>>> >>>>> I hope this helps. >>>>> >>>>> Regards, >>>>> Suraj >>>>> >>>>> >>>>>> >>>>>> [...] > >
From c0f7f1eddf15ac1df2e3f261f03206109eb07f17 Mon Sep 17 00:00:00 2001 From: Suraj Kharage <[email protected]> Date: Mon, 6 Apr 2026 15:29:28 +0530 Subject: [PATCH v7] Add support for INSERT ... SET syntax This commit adds support for INSERT ... SET syntax, which allows specifying column values using named assignments instead of requiring a separate column list and VALUES clause. Syntax: INSERT INTO table_name SET column1=value1, column2=value2, ...; This syntax provides a more convenient and readable alternative for single-row inserts, particularly when only specific columns need values. Columns not mentioned in the SET clause receive their default values (NULL if none), consistent with standard INSERT behavior. Features supported: - Basic syntax: INSERT INTO t SET col=val, ... - DEFAULT keyword: SET col=DEFAULT - NULL values: SET col=NULL - Expressions and functions: SET col=expr - Subqueries: SET col=(SELECT ...) - RETURNING clause: SET ... RETURNING * - ON CONFLICT: SET ... ON CONFLICT DO UPDATE/NOTHING - OVERRIDING clause: OVERRIDING { SYSTEM | USER } VALUE SET ... - Multi-row syntax: SET (col1=val1, col2=val2), (col1=val3, col2=val4) - Support for different column sets in multi-row inserts - Subfield/array-element targets: SET col.field=val, SET col[n]=val, including distinct targets of the same column in one row (e.g. c.x=1, c.y=2 or arr[1]=1, arr[2]=2) - psql tab completion of SET and of column names after SET Within one row, no target may be assigned twice. As with column-list INSERT, a whole-column assignment conflicts with any other assignment to the same column. In addition, repeating an identical subfield or array-element target (e.g. arr[1]=1, arr[1]=2) is rejected with "column ... specified more than once". This is deliberately stricter than column-list INSERT and UPDATE, which accept such repeats and keep the last value; rejecting them ensures no assigned expression is silently discarded. For multi-row SET, an array/field indirection target (e.g. arr[1], c.x) is only supported when every row uses that exact same target. Rows that omit the target, assign the whole column instead, or use a different element/field, are rejected with a clear error, since the underlying column list is shared across all rows and can't represent different per-row shapes for the same column. --- doc/src/sgml/ref/insert.sgml | 94 ++++++- src/backend/nodes/nodeFuncs.c | 2 + src/backend/parser/analyze.c | 221 +++++++++++++++ src/backend/parser/gram.y | 65 +++++ src/bin/psql/t/010_tab_completion.pl | 29 ++ src/bin/psql/tab-complete.in.c | 16 +- src/include/nodes/parsenodes.h | 2 + src/test/regress/expected/insert.out | 395 +++++++++++++++++++++++++++ src/test/regress/sql/insert.sql | 263 ++++++++++++++++++ 9 files changed, 1083 insertions(+), 4 deletions(-) diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 121a9edcb99..ae192832256 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -24,7 +24,9 @@ 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 } [, ...] ) [, ...] | <replaceable class="parameter">query</replaceable> | + SET <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] | + 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 [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ] { * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] @@ -65,6 +67,38 @@ INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replac associated with the explicit or implicit column list left-to-right. </para> + <para> + As an alternative to the <literal>VALUES</literal> clause, you can use the + <literal>SET</literal> clause to specify column values using named + assignments. The <literal>SET</literal> clause has the form + <literal>SET <replaceable>column_name</replaceable> = <replaceable>expression</replaceable></literal>, + with multiple column assignments separated by commas. This syntax is + particularly convenient when inserting a single row with values for + specific columns, as it eliminates the need to specify a separate + column list. When using <literal>SET</literal>, columns not mentioned + will receive their default values (NULL if none). The <literal>SET</literal> + syntax cannot be combined with an explicit column list. Within one + row, each target can be assigned only once: unlike an explicit column + list, <literal>SET</literal> rejects a repeated assignment to the same + subfield or array element, such as <literal>arr[1]=1, arr[1]=2</literal>. + </para> + + <para> + For multi-row inserts, the <literal>SET</literal> clause uses parentheses + to group each row's assignments: + <literal>SET (col1=val1, col2=val2), (col1=val3, col2=val4)</literal>. + Each row can specify a different set of columns; columns omitted from + a particular row will receive their default value (NULL if none). Column names + are matched across rows by name rather than position, so the order of + assignments within each row does not need to be consistent. If a target + is qualified with a subfield name or array subscript (see + <replaceable class="parameter">column_name</replaceable> below), every + row that assigns to that column must use that exact same subfield or + subscript: assigning a different subfield or element, assigning the + whole column instead, or omitting the column entirely, in some other + row of the same statement, is not supported and results in an error. + </para> + <para> Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value @@ -736,6 +770,59 @@ INSERT INTO films (code, title, did, date_prod, kind) VALUES </programlisting> </para> + <para> + To insert a single row using the <literal>SET</literal> syntax: + +<programlisting> +INSERT INTO films SET code='UA502', title='Bananas', did=105, + date_prod='1971-07-13', kind='Comedy', len='82 minutes'; +</programlisting> + </para> + + <para> + This example uses the <literal>SET</literal> syntax with + <literal>DEFAULT</literal> for some columns: + +<programlisting> +INSERT INTO films SET code='T_601', title='Yojimbo', did=106, + date_prod=DEFAULT, kind='Drama'; +</programlisting> + </para> + + <para> + The <literal>SET</literal> syntax can be used with expressions + and functions: + +<programlisting> +INSERT INTO films SET code='HG120', title=upper('the dinner game'), + did=140, date_prod=current_date, kind='Comedy'; +</programlisting> + </para> + + <para> + To insert multiple rows using the <literal>SET</literal> syntax, + enclose each row's assignments in parentheses: + +<programlisting> +INSERT INTO films SET + (code='B6717', title='Tampopo', did=110, date_prod='1985-02-10', kind='Comedy'), + (code='HG120', title='The Dinner Game', did=140, date_prod=DEFAULT, kind='Comedy'); +</programlisting> + </para> + + <para> + In multi-row <literal>SET</literal> syntax, each row can specify + different columns. Columns not specified in a row will use their + default values: + +<programlisting> +INSERT INTO films SET + (code='UA502', title='Bananas', did=105, kind='Comedy', len='82 minutes'), + (code='T_601', title='Yojimbo', did=106, kind='Drama'); +-- The second row's len column will receive its DEFAULT value +</programlisting> + </para> + <para> This example inserts some rows into table <structname>films</structname> from a table <structname>tmp_films</structname> @@ -900,6 +987,11 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') <xref linkend="sql-merge"/>. </para> + <para> + The <literal>SET</literal> form of <command>INSERT</command>, including + its multi-row form, is a <productname>PostgreSQL</productname> extension. + </para> + <para> The SQL standard specifies that <literal>OVERRIDING SYSTEM VALUE</literal> can only be specified if an identity column that is generated always diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 678b9a046ce..ba6097a0c26 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -4274,6 +4274,8 @@ raw_expression_tree_walker_impl(Node *node, return true; if (WALK(stmt->selectStmt)) return true; + if (WALK(stmt->setClause)) + return true; if (WALK(stmt->onConflictClause)) return true; if (WALK(stmt->returningClause)) diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 2dc81a8d3b2..00f1422112f 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -70,6 +70,8 @@ post_parse_analyze_hook_type post_parse_analyze_hook = NULL; static Query *transformOptionalSelectInto(ParseState *pstate, Node *parseTree); static Query *transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt); static Query *transformInsertStmt(ParseState *pstate, InsertStmt *stmt); +static void transformInsertSetClause(ParseState *pstate, List *setClause, + List **cols_p, List **valuesLists_p); static OnConflictExpr *transformOnConflictClause(ParseState *pstate, OnConflictClause *onConflictClause); static int count_rowexpr_columns(ParseState *pstate, Node *expr); @@ -641,6 +643,205 @@ transformDeleteStmt(ParseState *pstate, DeleteStmt *stmt) return qry; } +/* + * transformInsertSetClause - + * Transform INSERT ... SET clause into column list and VALUES lists. + * + * This function handles both single-row and multi-row SET syntax: + * Single row: INSERT INTO t SET c1=1, c2=2 + * Multi-row: INSERT INTO t SET (c1=1, c2=2), (c1=3, c2=4) + * + * The function supports different column sets across rows. For example: + * INSERT INTO t SET (c1=1, c2=2, c3=3), (c1=4, c2=5) + * This will generate: + * - Column list: c1, c2, c3 + * - Values: (1, 2, 3), (4, 5, DEFAULT) + * + * Missing columns in any row are filled with DEFAULT. + * + * A target's identity is its column name plus its indirection (".field" or + * "[subscript]"), so c.x and c.y, or arr[1] and arr[2], are distinct targets, + * while a whole-column target conflicts with any other target of that column. + * Repeating an identical target within a row is also rejected, so that no + * expression is silently discarded unanalyzed. + * + * All rows share one column list, as in a multi-row VALUES INSERT, so a + * partial target cannot be omitted from or replaced by a whole-column target + * in some row: that would amount to setting an element or field to DEFAULT. + * Hence if a partial target appears in a multi-row SET, every row must + * supply that exact same target. + */ +static void +transformInsertSetClause(ParseState *pstate, List *setClause, + List **cols_p, List **valuesLists_p) +{ + List *all_cols = NIL; /* List of all unique SET targets */ + List *valuesLists = NIL; + + /* + * First pass: collect all unique SET targets from all rows. We need + * to scan all rows first to determine the complete set of targets. + * Also check for conflicting targets within each row. + */ + foreach_node(List, set_clause, setClause) + { + List *row_cols = NIL; /* Targets seen in this row */ + + foreach_node(ResTarget, res, set_clause) + { + bool found = false; + + /* + * Check for a conflicting target in the same row. A + * whole-column assignment can't coexist with any other + * assignment to the same column name, and neither can a + * repeated identical partial target (unlike column-list + * INSERT, which allows it). Partial (subfield/subscript) + * assignments to different targets of the same column are fine. + */ + foreach_node(ResTarget, row_col, row_cols) + { + if (strcmp(row_col->name, res->name) == 0 && + (res->indirection == NIL || row_col->indirection == NIL || + equal(res->indirection, row_col->indirection))) + { + ereport(ERROR, + (errcode(ERRCODE_DUPLICATE_COLUMN), + errmsg("column \"%s\" specified more than once", + res->name), + parser_errposition(pstate, res->location))); + } + } + + /* Add to this row's target list */ + row_cols = lappend(row_cols, res); + + /* + * Check if we've already seen this exact target (same column + * name and same indirection) across all rows processed so + * far. + */ + foreach_node(ResTarget, existing, all_cols) + { + if (strcmp(existing->name, res->name) == 0 && + equal(existing->indirection, res->indirection)) + { + found = true; + break; + } + } + + /* If this is a new target across all rows, add it to our list */ + if (!found) + { + ResTarget *col = makeNode(ResTarget); + + col->name = res->name; + col->indirection = res->indirection; + col->val = NULL; + col->location = res->location; + all_cols = lappend(all_cols, col); + } + } + } + + /* + * Second pass (validation): if a partial (subfield/subscript) target is + * used anywhere and there's more than one row, require every row to + * supply that exact same target. See the function comment above for + * why this can't be relaxed while still flattening all rows into one + * shared column list. + */ + if (list_length(setClause) > 1) + { + foreach_node(ResTarget, col, all_cols) + { + if (col->indirection == NIL) + continue; + + foreach_node(List, set_clause, setClause) + { + bool row_has_target = false; + + foreach_node(ResTarget, res, set_clause) + { + if (strcmp(col->name, res->name) == 0 && + equal(col->indirection, res->indirection)) + { + row_has_target = true; + break; + } + } + + if (!row_has_target) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot omit or vary subfield or array element target \"%s\" between rows of a multi-row INSERT ... SET", + col->name), + errdetail("Every row must assign to exactly the same subfield or array element, because a subfield or array element cannot be set to DEFAULT."), + parser_errposition(pstate, col->location))); + } + } + } + + /* + * Third pass: for each row, create a values list matching the target + * order from all_cols. Use DEFAULT for any target not present in this + * row. + */ + foreach_node(List, set_clause, setClause) + { + List *vals = NIL; + + /* For each target in the complete target list */ + foreach_node(ResTarget, col, all_cols) + { + Node *val = NULL; + bool found = false; + + /* + * Search for this exact target in the current row. The first + * pass guarantees it appears at most once per row. + */ + foreach_node(ResTarget, res, set_clause) + { + if (strcmp(col->name, res->name) == 0 && + equal(col->indirection, res->indirection)) + { + val = res->val; + found = true; + break; + } + } + + if (found) + vals = lappend(vals, val); + else + { + /* + * The target is not present in this row. Fill with + * DEFAULT. This can only happen for a whole-column target + * here: the validation pass above already guarantees that + * any partial (indirection) target is present in every + * row, so we'll never generate an illegal "set an + * element/subfield to DEFAULT" from this fallback. + */ + SetToDefault *def = makeNode(SetToDefault); + + def->location = -1; + vals = lappend(vals, def); + } + } + + /* Add this row's values to the valuesLists */ + valuesLists = lappend(valuesLists, vals); + } + + /* Return the results */ + *cols_p = all_cols; + *valuesLists_p = valuesLists; +} + /* * transformInsertStmt - * transform an Insert Statement @@ -680,6 +881,26 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) qry->override = stmt->override; + /* + * If we have SET clause (INSERT ... SET col=val, ...), transform it + * into column list and VALUES list before further processing. + */ + if (stmt->setClause != NIL) + { + List *cols = NIL; + List *valuesLists = NIL; + + /* Transform SET clause into columns and values */ + transformInsertSetClause(pstate, stmt->setClause, &cols, &valuesLists); + + /* Create a SelectStmt with one or more VALUES rows */ + selectStmt = makeNode(SelectStmt); + selectStmt->valuesLists = valuesLists; + stmt->selectStmt = (Node *) selectStmt; + stmt->cols = cols; + stmt->setClause = NIL; /* clear it so we don't process again */ + } + /* * ON CONFLICT DO UPDATE and ON CONFLICT DO SELECT FOR UPDATE/SHARE * require UPDATE permission on the target relation. diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 0563453fe24..92460be4efb 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -558,6 +558,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <range> relation_expr_opt_alias %type <node> tablesample_clause opt_repeatable_clause %type <target> target_el set_target insert_column_item +%type <list> insert_set_clause insert_set_clause_list +%type <list> insert_set_clause_group insert_set_clause_group_list %type <str> generic_option_name %type <node> generic_option_arg @@ -12562,6 +12564,36 @@ insert_rest: $$->cols = NIL; $$->selectStmt = NULL; } + | OVERRIDING override_kind VALUE_P SET insert_set_clause_list + { + $$ = makeNode(InsertStmt); + $$->cols = NIL; + $$->selectStmt = NULL; + $$->override = $2; + $$->setClause = list_make1($5); + } + | OVERRIDING override_kind VALUE_P SET insert_set_clause_group_list + { + $$ = makeNode(InsertStmt); + $$->cols = NIL; + $$->selectStmt = NULL; + $$->override = $2; + $$->setClause = $5; + } + | SET insert_set_clause_group_list + { + $$ = makeNode(InsertStmt); + $$->cols = NIL; + $$->selectStmt = NULL; + $$->setClause = $2; + } + | SET insert_set_clause_list + { + $$ = makeNode(InsertStmt); + $$->cols = NIL; + $$->selectStmt = NULL; + $$->setClause = list_make1($2); + } ; override_kind: @@ -12838,6 +12870,39 @@ set_target_list: | set_target_list ',' set_target { $$ = lappend($1,$3); } ; +/* + * Grammar rules for INSERT ... SET syntax + * Supports both single-row and multi-row syntax: + * Single row: INSERT INTO table SET col1=val1, col2=val2 + * Multi-row: INSERT INTO table SET (col1=val1, ...), (col1=val3, ...) + * + * Each assignment target is a set_target, so it may carry subfield or + * subscript indirection (col.field = val, col[n] = val). + */ +insert_set_clause: + set_target '=' a_expr + { + $1->val = (Node *) $3; + $$ = list_make1($1); + } + ; + +insert_set_clause_list: + insert_set_clause { $$ = $1; } + | insert_set_clause_list ',' insert_set_clause + { $$ = list_concat($1, $3); } + ; + +insert_set_clause_group: + '(' insert_set_clause_list ')' { $$ = $2; } + ; + +insert_set_clause_group_list: + insert_set_clause_group { $$ = list_make1($1); } + | insert_set_clause_group_list ',' insert_set_clause_group + { $$ = lappend($1, $3); } + ; + /***************************************************************************** * diff --git a/src/bin/psql/t/010_tab_completion.pl b/src/bin/psql/t/010_tab_completion.pl index 1d2e5f5b92a..b260b138cda 100644 --- a/src/bin/psql/t/010_tab_completion.pl +++ b/src/bin/psql/t/010_tab_completion.pl @@ -347,6 +347,35 @@ check_completion( clear_query(); +# check INSERT ... SET completes column names, not GUC names +check_completion( + "INSERT INTO tab1 SET c\t\t", + qr/c1 +c2/, + "complete INSERT INTO <table> SET with column names"); + +clear_query(); + +check_completion( + "INSERT INTO tab1 SET c1 \t", + qr/INSERT INTO tab1 SET c1 = /, + "complete INSERT INTO <table> SET <column> with ="); + +clear_query(); + +check_completion( + "INSERT INTO tab1 OVERRIDING SYSTEM VALUE SET c\t\t", + qr/c1 +c2/, + "complete INSERT ... OVERRIDING ... VALUE SET with column names"); + +clear_query(); + +check_completion( + "INSERT INTO tab1 OVERRIDING SYSTEM VALUE SET c1 \t", + qr/INSERT INTO tab1 OVERRIDING SYSTEM VALUE SET c1 = /, + "complete INSERT ... OVERRIDING ... VALUE SET <column> with ="); + +clear_query(); + # check VersionedQuery infrastructure check_completion( "DROP PUBLIC\t \t\t", diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index f86684edc08..7b08150556a 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -4849,10 +4849,10 @@ match_previous_words(int pattern_id, /* * Complete INSERT INTO <table> with "(" or "VALUES" or "SELECT" or - * "TABLE" or "DEFAULT VALUES" or "OVERRIDING" + * "TABLE" or "DEFAULT VALUES" or "OVERRIDING" or "SET" */ else if (TailMatches("INSERT", "INTO", MatchAny)) - COMPLETE_WITH("(", "DEFAULT VALUES", "SELECT", "TABLE", "VALUES", "OVERRIDING"); + COMPLETE_WITH("(", "DEFAULT VALUES", "SELECT", "SET", "TABLE", "VALUES", "OVERRIDING"); /* * Complete INSERT INTO <table> (attribs) with "VALUES" or "SELECT" or @@ -4868,7 +4868,17 @@ match_previous_words(int pattern_id, /* Complete after OVERRIDING clause */ else if (TailMatches("OVERRIDING", MatchAny, "VALUE")) - COMPLETE_WITH("SELECT", "TABLE", "VALUES"); + COMPLETE_WITH("SELECT", "SET", "TABLE", "VALUES"); + + /* Complete INSERT INTO <table> SET with list of attributes */ + else if (TailMatches("INSERT", "INTO", MatchAny, "SET")) + COMPLETE_WITH_ATTR(prev2_wd); + else if (TailMatches("INSERT", "INTO", MatchAny, "OVERRIDING", MatchAny, "VALUE", "SET")) + COMPLETE_WITH_ATTR(prev5_wd); + /* INSERT INTO <table> SET <attr> = */ + else if (TailMatches("INSERT", "INTO", MatchAny, "SET", MatchAnyExcept("*=")) || + TailMatches("INSERT", "INTO", MatchAny, "OVERRIDING", MatchAny, "VALUE", "SET", MatchAnyExcept("*="))) + COMPLETE_WITH("="); /* Insert an open parenthesis after "VALUES" */ else if (TailMatches("VALUES") && !TailMatches("DEFAULT", "VALUES")) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 0debcd193ab..2dd7728978b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2155,6 +2155,8 @@ typedef struct InsertStmt ReturningClause *returningClause; /* RETURNING clause */ WithClause *withClause; /* WITH clause */ OverridingKind override; /* OVERRIDING clause */ + List *setClause; /* INSERT ... SET: one List of ResTarget per + * row */ } InsertStmt; /* ---------------------- diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index 75b8de79fce..d4b02ba7110 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -1096,3 +1096,398 @@ insert into returningwrtest values (2, 'foo') returning returningwrtest; (1 row) drop table returningwrtest; +-- +-- INSERT ... SET syntax tests +-- +create table insertsettest ( + id int, + name text, + salary int default 50000, + dept text, + created_at timestamp default now() +); +-- Basic INSERT SET syntax +insert into insertsettest set id=1, name='Alice', salary=60000, dept='Engineering'; +insert into insertsettest set name='Bob', id=2, dept='Sales', salary=55000; +-- INSERT SET with DEFAULT keyword +insert into insertsettest set id=3, name='Charlie', salary=DEFAULT, dept='HR'; +insert into insertsettest set id=4, name='David', dept='Marketing'; -- salary should use default +-- INSERT SET with NULL values +insert into insertsettest set id=5, name='Eve', salary=NULL, dept='Finance'; +insert into insertsettest set id=6, name=NULL, salary=70000, dept='IT'; +-- INSERT SET with expressions +insert into insertsettest set id=7, name='Frank', salary=50000+10000, dept='Engineering'; +insert into insertsettest set id=8, name=upper('grace'), salary=45000, dept=lower('SALES'); +-- INSERT SET with functions +insert into insertsettest set id=9, name=concat('John', ' ', 'Doe'), salary=80000, dept='Executive'; +-- INSERT SET with subqueries +insert into insertsettest set id=10, name='Kate', salary=(select max(salary) + 5000 from insertsettest), dept='Engineering'; +-- INSERT SET with column subset (others should be NULL or DEFAULT) +insert into insertsettest set id=11, name='Laura'; +insert into insertsettest set id=12, dept='Support'; +-- Verify all inserts +select id, name, salary, dept from insertsettest order by id; + id | name | salary | dept +----+----------+--------+------------- + 1 | Alice | 60000 | Engineering + 2 | Bob | 55000 | Sales + 3 | Charlie | 50000 | HR + 4 | David | 50000 | Marketing + 5 | Eve | | Finance + 6 | | 70000 | IT + 7 | Frank | 60000 | Engineering + 8 | GRACE | 45000 | sales + 9 | John Doe | 80000 | Executive + 10 | Kate | 85000 | Engineering + 11 | Laura | 50000 | + 12 | | 50000 | Support +(12 rows) + +-- INSERT SET with RETURNING clause +insert into insertsettest set id=13, name='Mike', salary=90000, dept='Management' returning id, name, salary, dept; + id | name | salary | dept +----+------+--------+------------ + 13 | Mike | 90000 | Management +(1 row) + +insert into insertsettest set id=14, name='Nancy', salary=95000, dept='Executive' returning id, name, salary; + id | name | salary +----+-------+-------- + 14 | Nancy | 95000 +(1 row) + +-- INSERT SET with ON CONFLICT DO UPDATE +create table insertsetpk ( + id int primary key, + value text, + counter int default 0 +); +insert into insertsetpk set id=1, value='first', counter=1; +insert into insertsetpk set id=2, value='second', counter=2; +-- Test ON CONFLICT DO UPDATE with INSERT SET +insert into insertsetpk set id=1, value='updated', counter=10 + on conflict (id) do update set value=excluded.value, counter=excluded.counter; +insert into insertsetpk set id=2, value='also updated', counter=20 + on conflict (id) do update set counter=insertsetpk.counter + excluded.counter; +select * from insertsetpk order by id; + id | value | counter +----+---------+--------- + 1 | updated | 10 + 2 | second | 22 +(2 rows) + +-- Test ON CONFLICT DO NOTHING with INSERT SET +insert into insertsetpk set id=1, value='ignored', counter=100 + on conflict (id) do nothing; +select * from insertsetpk order by id; + id | value | counter +----+---------+--------- + 1 | updated | 10 + 2 | second | 22 +(2 rows) + +-- INSERT SET with OVERRIDING SYSTEM VALUE (for identity columns) +create table insertsetgen ( + id int generated always as identity, + data text +); +-- This should fail (can't override without OVERRIDING clause) +insert into insertsetgen set id=100, data='test'; +ERROR: cannot insert a non-DEFAULT value into column "id" +DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS. +HINT: Use OVERRIDING SYSTEM VALUE to override. +-- This should work +insert into insertsetgen overriding system value set id=100, data='test'; +insert into insertsetgen set data='auto-generated'; +select * from insertsetgen order by id; + id | data +-----+---------------- + 1 | auto-generated + 100 | test +(2 rows) + +drop table insertsetgen; +-- INSERT SET with CHECK constraints +create table insertsetcheck ( + id int, + age int check (age >= 0 and age <= 150), + score int check (score between 0 and 100) +); +insert into insertsetcheck set id=1, age=25, score=85; +insert into insertsetcheck set id=2, age=30, score=92; +-- These should fail +insert into insertsetcheck set id=3, age=-5, score=50; -- age check fails +ERROR: new row for relation "insertsetcheck" violates check constraint "insertsetcheck_age_check" +DETAIL: Failing row contains (3, -5, 50). +insert into insertsetcheck set id=4, age=25, score=150; -- score check fails +ERROR: new row for relation "insertsetcheck" violates check constraint "insertsetcheck_score_check" +DETAIL: Failing row contains (4, 25, 150). +select * from insertsetcheck order by id; + id | age | score +----+-----+------- + 1 | 25 | 85 + 2 | 30 | 92 +(2 rows) + +drop table insertsetcheck; +-- INSERT SET with partitioned tables +create table insertsetpart ( + id int, + category text, + value int +) partition by list (category); +create table insertsetpart_a partition of insertsetpart for values in ('A'); +create table insertsetpart_b partition of insertsetpart for values in ('B'); +create table insertsetpart_c partition of insertsetpart for values in ('C'); +insert into insertsetpart set id=1, category='A', value=100; +insert into insertsetpart set id=2, category='B', value=200; +insert into insertsetpart set id=3, category='C', value=300; +insert into insertsetpart set id=4, category='A', value=150; +select tableoid::regclass, * from insertsetpart order by id; + tableoid | id | category | value +-----------------+----+----------+------- + insertsetpart_a | 1 | A | 100 + insertsetpart_b | 2 | B | 200 + insertsetpart_c | 3 | C | 300 + insertsetpart_a | 4 | A | 150 +(4 rows) + +drop table insertsetpart; +-- INSERT SET with inheritance +create table insertsetparent ( + id int, + parent_col text +); +create table insertsetchild ( + child_col text +) inherits (insertsetparent); +insert into insertsetparent set id=1, parent_col='parent data'; +insert into insertsetchild set id=2, parent_col='from child', child_col='child data'; +select * from insertsetparent order by id; + id | parent_col +----+------------- + 1 | parent data + 2 | from child +(2 rows) + +select * from insertsetchild; + id | parent_col | child_col +----+------------+------------ + 2 | from child | child data +(1 row) + +drop table insertsetchild; +drop table insertsetparent; +-- INSERT SET error cases +-- Duplicate column names (should fail) +insert into insertsettest set id=15, name='Test', id=16; +ERROR: column "id" specified more than once +LINE 1: insert into insertsettest set id=15, name='Test', id=16; + ^ +-- Non-existent column (should fail) +insert into insertsettest set id=15, nonexistent='value'; +ERROR: column "nonexistent" of relation "insertsettest" does not exist +LINE 1: insert into insertsettest set id=15, nonexistent='value'; + ^ +-- Type mismatch (should fail) +insert into insertsettest set id='not a number', name='Test'; +ERROR: invalid input syntax for type integer: "not a number" +LINE 1: insert into insertsettest set id='not a number', name='Test'... + ^ +-- Multi-column assignment syntax (should fail - this is UPDATE syntax) +insert into insertsettest set (id, name) = (20, 'Test'); +ERROR: syntax error at or near "," +LINE 1: insert into insertsettest set (id, name) = (20, 'Test'); + ^ +-- Multi-column assignment with subquery (should fail) +insert into insertsettest set (id, name) = (select 21, 'Test'); +ERROR: syntax error at or near "," +LINE 1: insert into insertsettest set (id, name) = (select 21, 'Test... + ^ +-- INSERT SET with CTE +with new_values as ( + select 15 as new_id, 'Oliver' as new_name, 85000 as new_salary +) +insert into insertsettest +set id=(select new_id from new_values), name=(select new_name from new_values), + salary=(select new_salary from new_values), dept='Sales'; +-- Verify CTE insert worked +select id, name, salary, dept from insertsettest where id = 15; + id | name | salary | dept +----+--------+--------+------- + 15 | Oliver | 85000 | Sales +(1 row) + +-- Multi-row INSERT SET syntax +create table insertsetmulti ( + c1 int, + c2 int, + c3 int +); +-- Basic multi-row with same column order +insert into insertsetmulti set (c1=1, c2=2, c3=3), (c1=4, c2=5, c3=6); +select * from insertsetmulti order by c1; + c1 | c2 | c3 +----+----+---- + 1 | 2 | 3 + 4 | 5 | 6 +(2 rows) + +-- Multi-row with different column orders +-- This tests that column-value matching works correctly across rows +insert into insertsetmulti set (c2=20, c1=10, c3=30), (c1=40, c3=60, c2=50), (c3=90, c2=80, c1=70); +select * from insertsetmulti order by c1; + c1 | c2 | c3 +----+----+---- + 1 | 2 | 3 + 4 | 5 | 6 + 10 | 20 | 30 + 40 | 50 | 60 + 70 | 80 | 90 +(5 rows) + +-- Multi-row with mixed expressions +insert into insertsetmulti set (c1=100, c2=200, c3=300), (c2=500, c1=400, c3=600); +select * from insertsetmulti order by c1; + c1 | c2 | c3 +-----+-----+----- + 1 | 2 | 3 + 4 | 5 | 6 + 10 | 20 | 30 + 40 | 50 | 60 + 70 | 80 | 90 + 100 | 200 | 300 + 400 | 500 | 600 +(7 rows) + +-- Test different column sets in multi-row INSERT SET +-- First row has all columns, second row has subset (c3 should get DEFAULT/NULL) +insert into insertsetmulti set (c1=1000, c2=2000, c3=3000), (c1=4000, c2=5000); +select * from insertsetmulti where c1 >= 1000 order by c1; + c1 | c2 | c3 +------+------+------ + 1000 | 2000 | 3000 + 4000 | 5000 | +(2 rows) + +-- First row has subset, second row has all columns (c2 in first row should get DEFAULT/NULL) +insert into insertsetmulti set (c1=1001, c3=3001), (c1=4001, c2=5001, c3=6001); +select * from insertsetmulti where c1 >= 1001 order by c1; + c1 | c2 | c3 +------+------+------ + 1001 | | 3001 + 4000 | 5000 | + 4001 | 5001 | 6001 +(3 rows) + +-- Different subsets in each row (union of all columns used, missing get DEFAULT/NULL) +insert into insertsetmulti set (c1=1002, c2=2002), (c1=4002, c3=6002); +select * from insertsetmulti where c1 >= 1002 order by c1; + c1 | c2 | c3 +------+------+------ + 1002 | 2002 | + 4000 | 5000 | + 4001 | 5001 | 6001 + 4002 | | 6002 +(4 rows) + +-- INSERT SET targeting different subfields/elements of the same column +-- (these are distinct targets, not duplicate column assignments) +create type insertset_comp as (x int, y int); +create table insertsetfield (id int primary key, c insertset_comp); +create table insertsetarr (id int primary key, arr int[]); +-- Different subfields of the same composite column +insert into insertsetfield set id=1, c.x=7, c.y=8; +select * from insertsetfield order by id; + id | c +----+------- + 1 | (7,8) +(1 row) + +-- Different elements of the same array column +insert into insertsetarr set id=1, arr[1]=30, arr[2]=40; +select * from insertsetarr order by id; + id | arr +----+--------- + 1 | {30,40} +(1 row) + +-- A whole-column assignment still conflicts with a partial assignment of +-- the same column (should fail) +insert into insertsetfield set id=2, c=row(1,2), c.x=9; +ERROR: column "c" specified more than once +LINE 1: insert into insertsetfield set id=2, c=row(1,2), c.x=9; + ^ +-- Repeating an identical array element target in one row is rejected, even +-- if the earlier expression would otherwise be invalid (should fail) +insert into insertsetarr set id=6, arr[1]=nonexistent_col, arr[1]=2; +ERROR: column "arr" specified more than once +LINE 1: ...nto insertsetarr set id=6, arr[1]=nonexistent_col, arr[1]=2; + ^ +insert into insertsetarr set id=6, arr[1]=1, arr[1]=2; +ERROR: column "arr" specified more than once +LINE 1: insert into insertsetarr set id=6, arr[1]=1, arr[1]=2; + ^ +-- Multi-row SET where rows target different elements of the same array +-- column can't be represented by the shared column list used for +-- multi-row SET (same limitation as INSERT ... (col-list) VALUES), so it +-- must be rejected rather than silently misassigning values (should fail) +insert into insertsetarr set (id=2, arr[1]=111), (id=3, arr[2]=222); +ERROR: cannot omit or vary subfield or array element target "arr" between rows of a multi-row INSERT ... SET +LINE 1: insert into insertsetarr set (id=2, arr[1]=111), (id=3, arr[... + ^ +DETAIL: Every row must assign to exactly the same subfield or array element, because a subfield or array element cannot be set to DEFAULT. +select * from insertsetarr order by id; + id | arr +----+--------- + 1 | {30,40} +(1 row) + +-- Multi-row SET where rows target the same element of the same array +-- column works fine, just like the equivalent column-list VALUES form +insert into insertsetarr set (id=4, arr[1]=1), (id=5, arr[1]=2); +select * from insertsetarr order by id; + id | arr +----+--------- + 1 | {30,40} + 4 | {1} + 5 | {2} +(3 rows) + +-- A row that omits an indirection-targeted column entirely, while other +-- rows in the same statement target an element/subfield of it, can't be +-- represented either: the shared target's expression is applied uniformly +-- to every row, so this row's "don't touch this column" can't be +-- distinguished from "set this element to its default" (which is itself +-- illegal). Must be rejected (should fail). +create table insertsetarrdef (id int primary key, arr int[] default array[1]); +insert into insertsetarrdef set (id=70, arr[1]=1), (id=71, arr[1]=2), (id=72); +ERROR: cannot omit or vary subfield or array element target "arr" between rows of a multi-row INSERT ... SET +LINE 1: insert into insertsetarrdef set (id=70, arr[1]=1), (id=71, a... + ^ +DETAIL: Every row must assign to exactly the same subfield or array element, because a subfield or array element cannot be set to DEFAULT. +select * from insertsetarrdef order by id; + id | arr +----+----- +(0 rows) + +-- Likewise, a row assigning the whole column (even to DEFAULT) can't be +-- mixed with another row that targets one of its elements (should fail) +insert into insertsetarrdef set (id=1, arr[1]=1), (id=2, arr=default); +ERROR: cannot omit or vary subfield or array element target "arr" between rows of a multi-row INSERT ... SET +LINE 1: insert into insertsetarrdef set (id=1, arr[1]=1), (id=2, arr... + ^ +DETAIL: Every row must assign to exactly the same subfield or array element, because a subfield or array element cannot be set to DEFAULT. +select * from insertsetarrdef order by id; + id | arr +----+----- +(0 rows) + +drop table insertsetfield; +drop table insertsetarr; +drop table insertsetarrdef; +drop type insertset_comp; +-- Cleanup +drop table insertsettest; +drop table insertsetpk; +drop table insertsetmulti; diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index 2b086eeb6d7..23e1b21d246 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -674,3 +674,266 @@ alter table returningwrtest2 drop c; alter table returningwrtest attach partition returningwrtest2 for values in (2); insert into returningwrtest values (2, 'foo') returning returningwrtest; drop table returningwrtest; + +-- +-- INSERT ... SET syntax tests +-- +create table insertsettest ( + id int, + name text, + salary int default 50000, + dept text, + created_at timestamp default now() +); + +-- Basic INSERT SET syntax +insert into insertsettest set id=1, name='Alice', salary=60000, dept='Engineering'; +insert into insertsettest set name='Bob', id=2, dept='Sales', salary=55000; + +-- INSERT SET with DEFAULT keyword +insert into insertsettest set id=3, name='Charlie', salary=DEFAULT, dept='HR'; +insert into insertsettest set id=4, name='David', dept='Marketing'; -- salary should use default + +-- INSERT SET with NULL values +insert into insertsettest set id=5, name='Eve', salary=NULL, dept='Finance'; +insert into insertsettest set id=6, name=NULL, salary=70000, dept='IT'; + +-- INSERT SET with expressions +insert into insertsettest set id=7, name='Frank', salary=50000+10000, dept='Engineering'; +insert into insertsettest set id=8, name=upper('grace'), salary=45000, dept=lower('SALES'); + +-- INSERT SET with functions +insert into insertsettest set id=9, name=concat('John', ' ', 'Doe'), salary=80000, dept='Executive'; + +-- INSERT SET with subqueries +insert into insertsettest set id=10, name='Kate', salary=(select max(salary) + 5000 from insertsettest), dept='Engineering'; + +-- INSERT SET with column subset (others should be NULL or DEFAULT) +insert into insertsettest set id=11, name='Laura'; +insert into insertsettest set id=12, dept='Support'; + +-- Verify all inserts +select id, name, salary, dept from insertsettest order by id; + +-- INSERT SET with RETURNING clause +insert into insertsettest set id=13, name='Mike', salary=90000, dept='Management' returning id, name, salary, dept; +insert into insertsettest set id=14, name='Nancy', salary=95000, dept='Executive' returning id, name, salary; + +-- INSERT SET with ON CONFLICT DO UPDATE +create table insertsetpk ( + id int primary key, + value text, + counter int default 0 +); + +insert into insertsetpk set id=1, value='first', counter=1; +insert into insertsetpk set id=2, value='second', counter=2; + +-- Test ON CONFLICT DO UPDATE with INSERT SET +insert into insertsetpk set id=1, value='updated', counter=10 + on conflict (id) do update set value=excluded.value, counter=excluded.counter; + +insert into insertsetpk set id=2, value='also updated', counter=20 + on conflict (id) do update set counter=insertsetpk.counter + excluded.counter; + +select * from insertsetpk order by id; + +-- Test ON CONFLICT DO NOTHING with INSERT SET +insert into insertsetpk set id=1, value='ignored', counter=100 + on conflict (id) do nothing; + +select * from insertsetpk order by id; + +-- INSERT SET with OVERRIDING SYSTEM VALUE (for identity columns) +create table insertsetgen ( + id int generated always as identity, + data text +); + +-- This should fail (can't override without OVERRIDING clause) +insert into insertsetgen set id=100, data='test'; + +-- This should work +insert into insertsetgen overriding system value set id=100, data='test'; +insert into insertsetgen set data='auto-generated'; + +select * from insertsetgen order by id; + +drop table insertsetgen; + +-- INSERT SET with CHECK constraints +create table insertsetcheck ( + id int, + age int check (age >= 0 and age <= 150), + score int check (score between 0 and 100) +); + +insert into insertsetcheck set id=1, age=25, score=85; +insert into insertsetcheck set id=2, age=30, score=92; + +-- These should fail +insert into insertsetcheck set id=3, age=-5, score=50; -- age check fails +insert into insertsetcheck set id=4, age=25, score=150; -- score check fails + +select * from insertsetcheck order by id; + +drop table insertsetcheck; + +-- INSERT SET with partitioned tables +create table insertsetpart ( + id int, + category text, + value int +) partition by list (category); + +create table insertsetpart_a partition of insertsetpart for values in ('A'); +create table insertsetpart_b partition of insertsetpart for values in ('B'); +create table insertsetpart_c partition of insertsetpart for values in ('C'); + +insert into insertsetpart set id=1, category='A', value=100; +insert into insertsetpart set id=2, category='B', value=200; +insert into insertsetpart set id=3, category='C', value=300; +insert into insertsetpart set id=4, category='A', value=150; + +select tableoid::regclass, * from insertsetpart order by id; + +drop table insertsetpart; + +-- INSERT SET with inheritance +create table insertsetparent ( + id int, + parent_col text +); + +create table insertsetchild ( + child_col text +) inherits (insertsetparent); + +insert into insertsetparent set id=1, parent_col='parent data'; +insert into insertsetchild set id=2, parent_col='from child', child_col='child data'; + +select * from insertsetparent order by id; +select * from insertsetchild; + +drop table insertsetchild; +drop table insertsetparent; + +-- INSERT SET error cases +-- Duplicate column names (should fail) +insert into insertsettest set id=15, name='Test', id=16; + +-- Non-existent column (should fail) +insert into insertsettest set id=15, nonexistent='value'; + +-- Type mismatch (should fail) +insert into insertsettest set id='not a number', name='Test'; + +-- Multi-column assignment syntax (should fail - this is UPDATE syntax) +insert into insertsettest set (id, name) = (20, 'Test'); + +-- Multi-column assignment with subquery (should fail) +insert into insertsettest set (id, name) = (select 21, 'Test'); + +-- INSERT SET with CTE +with new_values as ( + select 15 as new_id, 'Oliver' as new_name, 85000 as new_salary +) +insert into insertsettest +set id=(select new_id from new_values), name=(select new_name from new_values), + salary=(select new_salary from new_values), dept='Sales'; + +-- Verify CTE insert worked +select id, name, salary, dept from insertsettest where id = 15; + +-- Multi-row INSERT SET syntax +create table insertsetmulti ( + c1 int, + c2 int, + c3 int +); + +-- Basic multi-row with same column order +insert into insertsetmulti set (c1=1, c2=2, c3=3), (c1=4, c2=5, c3=6); +select * from insertsetmulti order by c1; + +-- Multi-row with different column orders +-- This tests that column-value matching works correctly across rows +insert into insertsetmulti set (c2=20, c1=10, c3=30), (c1=40, c3=60, c2=50), (c3=90, c2=80, c1=70); +select * from insertsetmulti order by c1; + +-- Multi-row with mixed expressions +insert into insertsetmulti set (c1=100, c2=200, c3=300), (c2=500, c1=400, c3=600); +select * from insertsetmulti order by c1; + +-- Test different column sets in multi-row INSERT SET +-- First row has all columns, second row has subset (c3 should get DEFAULT/NULL) +insert into insertsetmulti set (c1=1000, c2=2000, c3=3000), (c1=4000, c2=5000); +select * from insertsetmulti where c1 >= 1000 order by c1; + +-- First row has subset, second row has all columns (c2 in first row should get DEFAULT/NULL) +insert into insertsetmulti set (c1=1001, c3=3001), (c1=4001, c2=5001, c3=6001); +select * from insertsetmulti where c1 >= 1001 order by c1; + +-- Different subsets in each row (union of all columns used, missing get DEFAULT/NULL) +insert into insertsetmulti set (c1=1002, c2=2002), (c1=4002, c3=6002); +select * from insertsetmulti where c1 >= 1002 order by c1; + +-- INSERT SET targeting different subfields/elements of the same column +-- (these are distinct targets, not duplicate column assignments) +create type insertset_comp as (x int, y int); +create table insertsetfield (id int primary key, c insertset_comp); +create table insertsetarr (id int primary key, arr int[]); + +-- Different subfields of the same composite column +insert into insertsetfield set id=1, c.x=7, c.y=8; +select * from insertsetfield order by id; + +-- Different elements of the same array column +insert into insertsetarr set id=1, arr[1]=30, arr[2]=40; +select * from insertsetarr order by id; + +-- A whole-column assignment still conflicts with a partial assignment of +-- the same column (should fail) +insert into insertsetfield set id=2, c=row(1,2), c.x=9; + +-- Repeating an identical array element target in one row is rejected, even +-- if the earlier expression would otherwise be invalid (should fail) +insert into insertsetarr set id=6, arr[1]=nonexistent_col, arr[1]=2; +insert into insertsetarr set id=6, arr[1]=1, arr[1]=2; + +-- Multi-row SET where rows target different elements of the same array +-- column can't be represented by the shared column list used for +-- multi-row SET (same limitation as INSERT ... (col-list) VALUES), so it +-- must be rejected rather than silently misassigning values (should fail) +insert into insertsetarr set (id=2, arr[1]=111), (id=3, arr[2]=222); +select * from insertsetarr order by id; + +-- Multi-row SET where rows target the same element of the same array +-- column works fine, just like the equivalent column-list VALUES form +insert into insertsetarr set (id=4, arr[1]=1), (id=5, arr[1]=2); +select * from insertsetarr order by id; + +-- A row that omits an indirection-targeted column entirely, while other +-- rows in the same statement target an element/subfield of it, can't be +-- represented either: the shared target's expression is applied uniformly +-- to every row, so this row's "don't touch this column" can't be +-- distinguished from "set this element to its default" (which is itself +-- illegal). Must be rejected (should fail). +create table insertsetarrdef (id int primary key, arr int[] default array[1]); +insert into insertsetarrdef set (id=70, arr[1]=1), (id=71, arr[1]=2), (id=72); +select * from insertsetarrdef order by id; + +-- Likewise, a row assigning the whole column (even to DEFAULT) can't be +-- mixed with another row that targets one of its elements (should fail) +insert into insertsetarrdef set (id=1, arr[1]=1), (id=2, arr=default); +select * from insertsetarrdef order by id; + +drop table insertsetfield; +drop table insertsetarr; +drop table insertsetarrdef; +drop type insertset_comp; + +-- Cleanup +drop table insertsettest; +drop table insertsetpk; +drop table insertsetmulti; -- 2.47.3
