UPDATE myTable SET (myTable.*) = json_populate_record(...) ?
Dear Postgres Community,
cf. https://www.postgresql.org/docs/current/sql-update.html :
As far as I understand, I can
* UPDATE myTable SET col1 = val1, col2 = val2, ...
* UPDATE myTable SET (col1, col2, ...) = <ROW-or-subselect>
But I cannot, and I am missing that:
* UPDATE myTable SET (myTable.*) = <ROW-or-subselect>
* UPDATE myTable SET (col1, col2, ...) = json_populate_record(NULL::myTable, myJson) -- this is a function, not a ROW construct and not a subselect
This would be useful for some of my trigger code.
Regards,
fjf2002
On 8/3/26 12:09 AM, Färber, Franz-Josef (StMUK) wrote:
Dear Postgres Community,
cf. https://www.postgresql.org/docs/current/sql-update.html :
As far as I understand, I can
* UPDATE myTable SET col1 = val1, col2 = val2, ...
* UPDATE myTable SET (col1, col2, ...) = <ROW-or-subselect>But I cannot, and I am missing that:
* UPDATE myTable SET (myTable.*) = <ROW-or-subselect>
* UPDATE myTable SET (col1, col2, ...) = json_populate_record(NULL::myTable, myJson) -- this is a function, not a ROW construct and not a subselect
Make it a subselect? Not tested but:
UPDATE myTable SET (col1, col2, ...) = (select * from
json_populate_record(NULL::myTable, myJson))
This would be useful for some of my trigger code.
Regards,
fjf2002
--
Adrian Klaver
adrian.klaver@aklaver.com
Yes, the least grave of my items can be worked around by wrapping the function into a subselect, for whatever reason this might be necessary.
(As far as I know there are other places in Postgres where I can substitute a subselect by an expression / a function call, and I am curious: Why not here?)
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.*)
?
My first intention on posting was: Is there some way to acheive that?
If no, my second intention is a general critique on ... I think it is at least similar to what https://carlineng.com/?postid=sql-critique#blog calls " Lack of Orthogonality" in (Postgres-)SQL.
-----Ursprüngliche Nachricht-----
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Gesendet: Montag, 3. August 2026 17:00
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-general@lists.postgresql.org
Betreff: Re: UPDATE myTable SET (myTable.*) = json_populate_record(...) ?
On 8/3/26 12:09 AM, Färber, Franz-Josef (StMUK) wrote:
Dear Postgres Community,
cf. https://www.postgresql.org/docs/current/sql-update.html :
As far as I understand, I can
* UPDATE myTable SET col1 = val1, col2 = val2, ...
* UPDATE myTable SET (col1, col2, ...) = <ROW-or-subselect>But I cannot, and I am missing that:
* UPDATE myTable SET (myTable.*) = <ROW-or-subselect>
* UPDATE myTable SET (col1, col2, ...) = json_populate_record(NULL::myTable, myJson) -- this is a function, not a ROW construct and not a subselect
Make it a subselect? Not tested but:
UPDATE myTable SET (col1, col2, ...) = (select * from json_populate_record(NULL::myTable, myJson))
This would be useful for some of my trigger code.
Regards,
fjf2002
--
Adrian Klaver
adrian.klaver@aklaver.com
On 8/3/26 8:17 AM, Färber, Franz-Josef (StMUK) wrote:
Yes, the least grave of my items can be worked around by wrapping the function into a subselect, for whatever reason this might be necessary.
(As far as I know there are other places in Postgres where I can substitute a subselect by an expression / a function call, and I am curious: Why not here?)
Please provide examples of the cases where you can use a
function/expression.
As to why it is necessary the example shown here:
https://www.postgresql.org/docs/current/functions-json.html
shows that to extract the columns you need to do the SELECT.
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
"Do not include the table's name in the specification of a target column
— for example, UPDATE table_name SET table_name.col = 1 is invalid."
Show quoted text
?
My first intention on posting was: Is there some way to acheive that?
If no, my second intention is a general critique on ... I think it is at least similar to what https://carlineng.com/?postid=sql-critique#blog calls " Lack of Orthogonality" in (Postgres-)SQL.
-----Ursprüngliche Nachricht-----
Von: Adrian Klaver <adrian.klaver@aklaver.com>
Gesendet: Montag, 3. August 2026 17:00
An: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-general@lists.postgresql.org
Betreff: Re: UPDATE myTable SET (myTable.*) = json_populate_record(...) ?On 8/3/26 12:09 AM, Färber, Franz-Josef (StMUK) wrote:
Dear Postgres Community,
cf. https://www.postgresql.org/docs/current/sql-update.html :
As far as I understand, I can
* UPDATE myTable SET col1 = val1, col2 = val2, ...
* UPDATE myTable SET (col1, col2, ...) = <ROW-or-subselect>But I cannot, and I am missing that:
* UPDATE myTable SET (myTable.*) = <ROW-or-subselect>
* UPDATE myTable SET (col1, col2, ...) = json_populate_record(NULL::myTable, myJson) -- this is a function, not a ROW construct and not a subselectMake it a subselect? Not tested but:
UPDATE myTable SET (col1, col2, ...) = (select * from json_populate_record(NULL::myTable, myJson))
This would be useful for some of my trigger code.
Regards,
fjf2002--
Adrian Klaver
adrian.klaver@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
Thank you for your thoughts on it. What I was really doing was:
1) I have an audit table that gets filled with JSON objects. The Trigger Procedure does in essence:
INSERT INTO audit_log
(timestamp, tg_relid, tg_op, old, new)
VALUES
(now(), TG_RELID, TG_OP, to_json(OLD), to_json(NEW));
2) I thought about un-doing these operations in reverse order. Time-Travel, so to say. For example to un-do an UPDATE statement, I would have
UPDATE %1$s
SET (%2$s) = (SELECT * FROM json_populate_record(NULL::%1$s, $1))
WHERE json_populate_record(NULL::%1$s, $2) = (%1$s.*)
(dynamic SQL, constructed with EXECUTE format(...) USING old, new ; where "old" and "new" are JSON values from the audit_log table.)
Here you see:
* The WHERE condition can be written quite compact, using equality on row types.
* But the SET clause can't: Does not support a row type on the left hand, and does not support a row type returning function on the right hand.
(Yes I know the SET assigment is a semantically different thing to the WHERE equality comparison operator.)
THAT is the thing that occured to me as somehow inconsequent.
-----Ursprüngliche Nachricht-----
Von: Tom Lane <tgl@sss.pgh.pa.us>
Gesendet: Montag, 3. August 2026 18:49
An: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>; pgsql-general@lists.postgresql.org
Betreff: Re: AW: UPDATE myTable SET (myTable.*) = json_populate_record(...) ?
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
On Mon, Aug 3, 2026 at 4:19 AM Färber, Franz-Josef (StMUK) <
Franz-Josef.Faerber@stmuk.bayern.de> wrote:
Dear Postgres Community,
cf. https://www.postgresql.org/docs/current/sql-update.html :
As far as I understand, I can
* UPDATE myTable SET col1 = val1, col2 = val2, ...
* UPDATE myTable SET (col1, col2, ...) = <ROW-or-subselect>But I cannot, and I am missing that:
* UPDATE myTable SET (myTable.*) = <ROW-or-subselect>
* UPDATE myTable SET (col1, col2, ...) =
json_populate_record(NULL::myTable, myJson) -- this is a function, not a
ROW construct and not a subselect
What you *can* do is insert from json_populate_record, then write an ON
CONFLICT clause to set the rest of the columns. It's possible to do this
in a generic way with dynamic SQL if you assume that you are matching the
table's primary key and the rest of the columns are to be updated.
As others mentioned, SQL doesn't really have this, but if you are very
proficient with pl/pgsql you can work around it.
merlin