From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: AW: UPDATE myTable SET (myTable.*) = json_populate_record(...) ?
Date: Mon, 03 Aug 2026 12:48:56 -0400
Message-ID: <733999.1785775736@sss.pgh.pa.us> (raw)
In-Reply-To: <e5e0b946-33a5-4385-9e7b-1121dea51828@aklaver.com>
References: <688383b93cf74558b5b9484ca3c3996a@stmuk.bayern.de>
<a0519983-dfc1-4660-be0e-985b07bfae02@aklaver.com>
<c84d894abc8b4ababe03e1c08255c3b6@stmuk.bayern.de>
<e5e0b946-33a5-4385-9e7b-1121dea51828@aklaver.com>
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 8/3/26 8:17 AM, Färber, Franz-Josef (StMUK) wrote:
>> But what about my other item... what about somehow preventing to write all column names, i. e. changing ...
>> SET (col1, col2, ...)
>> ... to something like ...
>> SET (myTable.*)
> Best bet is you are seeing the effect of, from here:
> https://www.postgresql.org/docs/18/sql-update.html
The SQL standard does not allow writing "*" there, and that's
a restriction I agree with. If you write "SELECT *" and the
output has more or fewer columns, or columns in a different
order, than you expected, no great harm is done to your database.
If we allowed "UPDATE SET (*) = blah, blah" and the same type
of confusion occurred, you might completely destroy your table.
Writing out the target columns explicitly is a small price to
reduce the odds of mistakes.
(I'm too lazy to search for the exact quote, but there's something
in Brooks' classic "The Mythical Man-month" to the effect that,
if a fairy came to a programmer and offered to make all his code
bug-free if he'd agree to type it in three times, any programmer
in the world would instantly take that bargain.)
Having said that, the error checks in transformMultiAssignRef
could probably be ordered better. For instance:
postgres=# create table tab (a int, b int);
CREATE TABLE
postgres=# update tab set (*) = row(1,2);
ERROR: syntax error at or near "*"
LINE 1: update tab set (*) = row(1,2);
^
postgres=# update tab set (tab.*) = row(1,2);
ERROR: number of columns does not match number of values
LINE 1: update tab set (tab.*) = row(1,2);
^
postgres=# update tab set (tab.*) = row(1);
ERROR: column "tab" of relation "tab" does not exist
LINE 1: update tab set (tab.*) = row(1);
^
HINT: SET target columns cannot be qualified with the relation name.
We ought to be complaining that the SET target is inherently invalid
before we start thinking about whether it matches the source value.
regards, tom lane
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: pgsql-general@postgresql.org
Cc: tgl@sss.pgh.pa.us, adrian.klaver@aklaver.com, Franz-Josef.Faerber@stmuk.bayern.de, pgsql-general@lists.postgresql.org
Subject: Re: AW: UPDATE myTable SET (myTable.*) = json_populate_record(...) ?
In-Reply-To: <733999.1785775736@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox