public inbox for [email protected]
help / color / mirror / Atom feedFrom: Suraj Kharage <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: [PATCH] Add support for INSERT ... SET syntax
Date: Mon, 30 Mar 2026 16:34:27 +0530
Message-ID: <CAF1DzPXhKc15Toa06MTT7NYOAEq8Ucr=RYe5V7X2FkwGMmjD6w@mail.gmail.com> (raw)
Hi,
I would like to propose adding support for an alternative INSERT syntax
that uses named column assignments via a SET clause. This provides a more
convenient and readable way to write inserts, particularly when only
specific columns need values.
Currently, PostgreSQL requires INSERT statements to separate the column
list from the values:
INSERT INTO users (name, email, status) VALUES ('Alice', '
[email protected]', 'active');
For inserts with many columns or where only a subset of columns are
specified, the proposed SET syntax offers better readability by keeping
column names adjacent to their values:
INSERT INTO users SET name='Alice', email='[email protected]',
status='active';
Proposed Syntax:
INSERT INTO table_name
SET (column1=value1, column2=value2, ...), (, ...)
[ ON CONFLICT ... ]
[ RETURNING ... ];
Below INSERT features are supported:
- DEFAULT keyword: SET col=DEFAULT
- Expressions and functions: SET col=expr, col2=function(...)
- Subqueries: SET col=(SELECT ...)
- RETURNING clause
- ON CONFLICT DO UPDATE/NOTHING
- OVERRIDING SYSTEM VALUE
- Multi-row syntax: SET (col1=val1, col2=val2), (col1=val3, col2=val4)
Columns not mentioned receive their default values or NULL, consistent with
standard INSERT behavior.
I've attached the patch. Looking forward to your feedback.
--
Thanks & Regards,
Suraj kharage,
enterprisedb.com <https://www.enterprisedb.com/;
Attachments:
[application/octet-stream] v1-0001-Add-support-for-INSERT-.-SET-syntax.patch (30.1K, 3-v1-0001-Add-support-for-INSERT-.-SET-syntax.patch)
download | inline diff:
From 4233e2ef5d1223f0f3853c802df31b0015ed8bdd Mon Sep 17 00:00:00 2001
From: Suraj Kharage <[email protected]>
Date: Wed, 25 Mar 2026 16:46:14 +0530
Subject: [PATCH v1] 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)
---
doc/src/sgml/ref/insert.sgml | 45 ++++-
src/backend/parser/analyze.c | 125 +++++++++++++
src/backend/parser/gram.y | 63 +++++++
src/bin/psql/tab-complete.in.c | 6 +-
src/include/nodes/parsenodes.h | 1 +
src/test/regress/expected/insert.out | 265 +++++++++++++++++++++++++++
src/test/regress/sql/insert.sql | 194 ++++++++++++++++++++
7 files changed, 695 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 121a9edcb99..faf7e17bfa4 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -24,7 +24,8 @@ 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 } [, ...] ), [, ...] }
[ 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 +66,19 @@ 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>
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 +750,35 @@ 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>
This example inserts some rows into table
<structname>films</structname> from a table <structname>tmp_films</structname>
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index ad31dee2686..4ebb4bfdb6f 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -668,6 +668,131 @@ 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.
+ *
+ * Supports both single-row and multi-row 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)
+ */
+ if (stmt->setClauseList != NIL)
+ {
+ List *cols = NIL;
+ List *valuesLists = NIL;
+ ListCell *outer_lc;
+ bool first_row = true;
+ int expected_cols = 0;
+
+ /*
+ * Transform SET clause list(s) into column list and VALUES lists.
+ * setClauseList is a list of lists, where each inner list represents
+ * one row of SET clauses.
+ */
+ foreach(outer_lc, stmt->setClauseList)
+ {
+ List *set_clause = (List *) lfirst(outer_lc);
+ List *vals = NIL;
+ ListCell *set_lc;
+ int col_count = 0;
+
+ if (first_row)
+ {
+ /*
+ * First row: extract column names to build the cols list and
+ * collect values in the order they appear.
+ */
+ foreach(set_lc, set_clause)
+ {
+ ResTarget *res = (ResTarget *) lfirst(set_lc);
+ ResTarget *col;
+
+ col_count++;
+
+ /* Create column reference for cols list */
+ col = makeNode(ResTarget);
+ col->name = res->name;
+ col->indirection = res->indirection;
+ col->val = NULL;
+ col->location = res->location;
+ cols = lappend(cols, col);
+
+ /* Add value expression to vals list for this row */
+ vals = lappend(vals, res->val);
+ }
+
+ expected_cols = col_count;
+ first_row = false;
+ }
+ else
+ {
+ /*
+ * Subsequent rows: match column names with the first row's order.
+ * Build a mapping from column names to values for this row, then
+ * extract values in the same order as the first row.
+ */
+ List *col_val_pairs = NIL;
+ ListCell *col_lc;
+
+ /* First pass: collect all column-value pairs from this row */
+ foreach(set_lc, set_clause)
+ {
+ ResTarget *res = (ResTarget *) lfirst(set_lc);
+
+ col_count++;
+ col_val_pairs = lappend(col_val_pairs, res);
+ }
+
+ /* Check column count matches first row */
+ if (col_count != expected_cols)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("INSERT ... SET rows must have the same number of columns"),
+ errhint("First row has %d columns, but this row has %d columns.",
+ expected_cols, col_count)));
+
+ /*
+ * Second pass: iterate through first row's column order and find
+ * matching values from current row.
+ */
+ foreach(col_lc, cols)
+ {
+ ResTarget *first_row_col = (ResTarget *) lfirst(col_lc);
+ bool found = false;
+ ListCell *pair_lc;
+
+ foreach(pair_lc, col_val_pairs)
+ {
+ ResTarget *curr_row_res = (ResTarget *) lfirst(pair_lc);
+
+ if (strcmp(first_row_col->name, curr_row_res->name) == 0)
+ {
+ vals = lappend(vals, curr_row_res->val);
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" specified in first row but missing in subsequent row",
+ first_row_col->name)));
+ }
+ }
+
+ /* Add this row's values to the valuesLists */
+ valuesLists = lappend(valuesLists, vals);
+ }
+
+ /* 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 0fea726cdd5..2be697411d6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -561,6 +561,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> set_clause_item set_clause_item_list
+%type <list> set_clause_group set_clause_group_list
%type <str> generic_option_name
%type <node> generic_option_arg
@@ -13050,6 +13052,36 @@ insert_rest:
$$->cols = NIL;
$$->selectStmt = NULL;
}
+ | OVERRIDING override_kind VALUE_P SET set_clause_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = NIL;
+ $$->selectStmt = NULL;
+ $$->override = $2;
+ $$->setClauseList = list_make1($5);
+ }
+ | OVERRIDING override_kind VALUE_P SET set_clause_group_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = NIL;
+ $$->selectStmt = NULL;
+ $$->override = $2;
+ $$->setClauseList = $5;
+ }
+ | SET set_clause_group_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = NIL;
+ $$->selectStmt = NULL;
+ $$->setClauseList = $2;
+ }
+ | SET set_clause_list
+ {
+ $$ = makeNode(InsertStmt);
+ $$->cols = NIL;
+ $$->selectStmt = NULL;
+ $$->setClauseList = list_make1($2);
+ }
;
override_kind:
@@ -13326,6 +13358,37 @@ set_target_list:
| set_target_list ',' set_target { $$ = lappend($1,$3); }
;
+/*
+ * Grammar rules for multi-row INSERT ... SET syntax
+ * Supports: INSERT INTO table SET (col1=val1, col2=val2), (col1=val3, col2=val4)
+ *
+ * Note: we use set_clause_item (not set_clause) to avoid conflicts with
+ * the multi-column assignment syntax in UPDATE: (col1, col2) = (val1, val2)
+ */
+set_clause_item:
+ set_target '=' a_expr
+ {
+ $1->val = (Node *) $3;
+ $$ = list_make1($1);
+ }
+ ;
+
+set_clause_item_list:
+ set_clause_item { $$ = $1; }
+ | set_clause_item_list ',' set_clause_item
+ { $$ = list_concat($1, $3); }
+ ;
+
+set_clause_group:
+ '(' set_clause_item_list ')' { $$ = $2; }
+ ;
+
+set_clause_group_list:
+ set_clause_group { $$ = list_make1($1); }
+ | set_clause_group_list ',' 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 523d3f39fc5..4c2dd9e3fcc 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -4916,10 +4916,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
@@ -4935,7 +4935,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 df431220ac5..825ebace025 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2197,6 +2197,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..d36b51d4556 100644
--- a/src/test/regress/expected/insert.out
+++ b/src/test/regress/expected/insert.out
@@ -1096,3 +1096,268 @@ 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'...
+ ^
+-- 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 (test bug fix)
+-- 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)
+
+-- Error case: rows must have same columns
+insert into insertsetmulti set (c1=1, c2=2), (c1=3, c2=4, c3=5);
+ERROR: INSERT ... SET rows must have the same number of columns
+HINT: First row has 2 columns, but this row has 3 columns.
+-- Error case: subsequent row missing column from first row
+insert into insertsetmulti set (c1=1, c2=2, c3=3), (c1=4, c2=5);
+ERROR: INSERT ... SET rows must have the same number of columns
+HINT: First row has 3 columns, but this row has 2 columns.
+-- 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..261bce494b8 100644
--- a/src/test/regress/sql/insert.sql
+++ b/src/test/regress/sql/insert.sql
@@ -674,3 +674,197 @@ 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';
+
+-- 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 (test bug fix)
+-- 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;
+
+-- Error case: rows must have same columns
+insert into insertsetmulti set (c1=1, c2=2), (c1=3, c2=4, c3=5);
+
+-- Error case: subsequent row missing column from first row
+insert into insertsetmulti set (c1=1, c2=2, c3=3), (c1=4, c2=5);
+
+-- 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]
Subject: Re: [PATCH] Add support for INSERT ... SET syntax
In-Reply-To: <CAF1DzPXhKc15Toa06MTT7NYOAEq8Ucr=RYe5V7X2FkwGMmjD6w@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