public inbox for [email protected]
help / color / mirror / Atom feedFrom: Triveni N <[email protected]>
To: pgsql-hackers <[email protected]>
Cc: Suraj Kharage <[email protected]>
Cc: [email protected]
Cc: [email protected] <[email protected]>
Cc: [email protected]
Cc: Ajay Pal <[email protected]>
Subject: Fwd: [PATCH] Add support for INSERT ... SET syntax
Date: Fri, 19 Jun 2026 19:05:03 +0530
Message-ID: <CAJrT2Tt32+hTEECzSmMV+fp0Xw8U=cXoQO-yCK27R35kLx6RbQ@mail.gmail.com> (raw)
In-Reply-To: <CAC6VRoZLEK0Ps=z21_q9zfp=Y+Ej6JR=KcScUyDbWu3m_wi7Hg@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CABRHmytPtGAv9weY8_7U_6JPOeYx1+Kq=ojMUYqXuSJB95Hvyg@mail.gmail.com>
<CAF1DzPUHYf=zUSrrnT5C+YBjWr5KYEFhG_u1P-Xh_8gc_aYQiA@mail.gmail.com>
<CAF1DzPVM2-wOCKRanNS01S4jFXsiYyx0Jq_iOR7L=F6gkH+bxQ@mail.gmail.com>
<CAC6VRoZLEK0Ps=z21_q9zfp=Y+Ej6JR=KcScUyDbWu3m_wi7Hg@mail.gmail.com>
From: Suraj Kharage <[email protected]>
Date: Mon, Apr 6, 2026 at 4:13 PM
Subject: Re: [PATCH] Add support for INSERT ... SET syntax
To: Ajay Pal <[email protected]>
Cc: Álvaro Herrera <[email protected]>, Andrew Dunstan <
[email protected]>, David G. Johnston <[email protected]>,
PostgreSQL Hackers <[email protected]>
Hi,
Rebased the patch with some documentation changes.
Also, added commitfest entry - https://commitfest.postgresql.org/patch/6635/
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/;
Hi,
Sharing an update on testing.
Below are the areas that I’ve covered:
**Core syntax**
- Basic SET syntax (single row, with and without parentheses)
- Column order independence
- Multi-row insertion — SET (col=val, ...), (col=val, ...) syntax
**Value types**
- DEFAULT keyword and implicit defaults (column omission)
- Expressions, functions, and subqueries (including deeply nested)
- NULL values, array columns, composite types, and domain types
**Clauses & advanced features**
- RETURNING clause (single and multi-row)
- ON CONFLICT DO NOTHING / DO UPDATE
- OVERRIDING SYSTEM VALUE
- CTE (WITH clause)
- EXPLAIN / EXPLAIN ANALYZE
**Schema & constraints**
- CHECK constraints and column-level privileges
- Foreign key constraints (valid and failing cases)
- Partitioned tables, table inheritance, and schema-qualified table names
- Quoted and reserved keyword column names
**Procedural contexts**
- PL/pgSQL functions and DO blocks (including multi-row)
- Dynamic SQL via EXECUTE with parameters
- Prepared statements (PREPARE + EXECUTE)
- SAVEPOINTs and rollback behavior
**Other**
- Updatable views
- BEFORE and AFTER INSERT triggers
- Negative cases covering syntax errors, constraint violations, and
privilege violations
Note: Issue reported by Ajay Pal has been resolved in v3 patch.
--
Warm regards,
Triveni
Attachments:
[application/x-patch] v3-0001-Add-support-for-INSERT-.-SET-syntax.patch (35.6K, 3-v3-0001-Add-support-for-INSERT-.-SET-syntax.patch)
download | inline diff:
From dd50876899f280b40d4587d47a2694dc3fbb60e7 Mon Sep 17 00:00:00 2001
From: Suraj Kharage <[email protected]>
Date: Mon, 6 Apr 2026 15:29:28 +0530
Subject: [PATCH v3] 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 or
NULL, 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 SYSTEM VALUE: OVERRIDING SYSTEM VALUE SET ...
- Multi-row syntax: SET (col1=val1, col2=val2), (col1=val3, col2=val4)
- Support for different column sets in multi-row inserts
---
doc/src/sgml/ref/insert.sgml | 80 ++++++-
src/backend/nodes/nodeFuncs.c | 2 +
src/backend/parser/analyze.c | 160 ++++++++++++++
src/backend/parser/gram.y | 64 ++++++
src/bin/psql/tab-complete.in.c | 6 +-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/insert.out | 298 +++++++++++++++++++++++++++
src/test/regress/sql/insert.sql | 207 +++++++++++++++++++
8 files changed, 814 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 121a9edcb99..8111c040cc2 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,29 @@ 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 or NULL. The <literal>SET</literal>
+ syntax cannot be combined with an explicit column list.
+ </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 or NULL. 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.
+ </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 +761,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 and date_prod columns will receive DEFAULT values
+</programlisting>
+ </para>
+
<para>
This example inserts some rows into table
<structname>films</structname> from a table <structname>tmp_films</structname>
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index c0b880ec233..61786c1f3f5 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -4326,6 +4326,8 @@ raw_expression_tree_walker_impl(Node *node,
return true;
if (WALK(stmt->selectStmt))
return true;
+ if (WALK(stmt->setClauseList))
+ 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 84deed9aaa6..37673c05d4d 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -76,6 +76,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 *setClauseList,
+ List **cols_p, List **valuesLists_p);
static OnConflictExpr *transformOnConflictClause(ParseState *pstate,
OnConflictClause *onConflictClause);
static ForPortionOfExpr *transformForPortionOfClause(ParseState *pstate,
@@ -645,6 +647,144 @@ 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.
+ */
+static void
+transformInsertSetClause(ParseState *pstate, List *setClauseList,
+ List **cols_p, List **valuesLists_p)
+{
+ List *all_cols = NIL; /* List of all unique column names */
+ List *valuesLists = NIL;
+ ListCell *outer_lc;
+ ListCell *lc;
+
+ /*
+ * First pass: collect all unique column names from all rows.
+ * We need to scan all rows first to determine the complete set of columns.
+ * Also check for duplicate columns within each row.
+ */
+ foreach(outer_lc, setClauseList)
+ {
+ List *set_clause = (List *) lfirst(outer_lc);
+ List *row_cols = NIL; /* Columns seen in this row */
+ ListCell *set_lc;
+
+ foreach(set_lc, set_clause)
+ {
+ ResTarget *res = (ResTarget *) lfirst(set_lc);
+ bool found = false;
+ ListCell *col_lc;
+
+ /* Check for duplicate column in the same row */
+ foreach(col_lc, row_cols)
+ {
+ ResTarget *row_col = (ResTarget *) lfirst(col_lc);
+
+ if (strcmp(row_col->name, res->name) == 0)
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" specified more than once",
+ res->name),
+ parser_errposition(pstate, res->location)));
+ }
+ }
+
+ /* Add to this row's column list */
+ row_cols = lappend(row_cols, res);
+
+ /* Check if we've already seen this column name across all rows */
+ foreach(col_lc, all_cols)
+ {
+ ResTarget *existing = (ResTarget *) lfirst(col_lc);
+
+ if (strcmp(existing->name, res->name) == 0)
+ {
+ found = true;
+ break;
+ }
+ }
+
+ /* If this is a new column 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: for each row, create a values list matching the column order
+ * from all_cols. Use DEFAULT for any columns not present in this row.
+ */
+ foreach(outer_lc, setClauseList)
+ {
+ List *set_clause = (List *) lfirst(outer_lc);
+ List *vals = NIL;
+
+ /* For each column in the complete column list */
+ foreach(lc, all_cols)
+ {
+ ResTarget *col = (ResTarget *) lfirst(lc);
+ bool found = false;
+ ListCell *set_lc;
+
+ /* Search for this column in the current row */
+ foreach(set_lc, set_clause)
+ {
+ ResTarget *res = (ResTarget *) lfirst(set_lc);
+
+ if (strcmp(col->name, res->name) == 0)
+ {
+ /* Found it - use the provided value */
+ vals = lappend(vals, res->val);
+ found = true;
+ break;
+ }
+ }
+
+ /*
+ * If the column is not present in this row, use DEFAULT.
+ * Create a SetToDefault node to represent the DEFAULT keyword.
+ */
+ if (!found)
+ {
+ 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
@@ -684,6 +824,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->setClauseList != NIL)
+ {
+ List *cols = NIL;
+ List *valuesLists = NIL;
+
+ /* Transform SET clause into columns and values */
+ transformInsertSetClause(pstate, stmt->setClauseList, &cols, &valuesLists);
+
+ /* Create a SelectStmt with multiple VALUES rows */
+ selectStmt = makeNode(SelectStmt);
+ selectStmt->valuesLists = valuesLists;
+ stmt->selectStmt = (Node *) selectStmt;
+ stmt->cols = cols;
+ stmt->setClauseList = 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 f4a08baa95a..3a6190f5fa6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -563,6 +563,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> for_portion_of_clause
%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
@@ -13055,6 +13057,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;
+ $$->setClauseList = list_make1($5);
+ }
+ | OVERRIDING override_kind VALUE_P SET insert_set_clause_group_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = NIL;
+ $$->selectStmt = NULL;
+ $$->override = $2;
+ $$->setClauseList = $5;
+ }
+ | SET insert_set_clause_group_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = NIL;
+ $$->selectStmt = NULL;
+ $$->setClauseList = $2;
+ }
+ | SET insert_set_clause_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = NIL;
+ $$->selectStmt = NULL;
+ $$->setClauseList = list_make1($2);
+ }
;
override_kind:
@@ -13365,6 +13397,38 @@ 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, col2=val2), (col1=val3, col2=val4)
+ *
+ * These rules are INSERT-specific and only allow simple column=value assignments.
+ */
+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/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 53bf1e21721..06a2b4c1f8d 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -4920,10 +4920,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
@@ -4939,7 +4939,7 @@ 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");
/* 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 91377a6cde3..73110e97ab6 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2216,6 +2216,7 @@ typedef struct InsertStmt
ReturningClause *returningClause; /* RETURNING clause */
WithClause *withClause; /* WITH clause */
OverridingKind override; /* OVERRIDING clause */
+ List *setClauseList; /* SET clause list (for INSERT ... SET syntax) */
} InsertStmt;
/* ----------------------
diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out
index 75b8de79fce..9806a8a9e8f 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -1096,3 +1096,301 @@ 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 generated 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
+select new_id, new_name, new_salary, 'Sales' from new_values;
+-- Verify CTE insert worked (not using SET syntax, but for completeness)
+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)
+
+-- 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..323ced05fba 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -674,3 +674,210 @@ 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 generated 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
+select new_id, new_name, new_salary, 'Sales' from new_values;
+
+-- Verify CTE insert worked (not using SET syntax, but for completeness)
+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;
+
+-- Cleanup
+drop table insertsettest;
+drop table insertsetpk;
+drop table insertsetmulti;
--
2.47.3
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Fwd: [PATCH] Add support for INSERT ... SET syntax
In-Reply-To: <CAJrT2Tt32+hTEECzSmMV+fp0Xw8U=cXoQO-yCK27R35kLx6RbQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox