7 ms·
What's the significance of the `update .. set (..) = row (..) ..` syntax? update comment set modified = now(), body = 'edited comment' where id = 123; vs
by walrus 9y ago
What's the significance of the `update .. set (..) = row (..) ..` syntax?
update comment set modified = now(), body = 'edited comment' where id = 123;
vs
update comment set (modified, body) = row (now(), 'edited comment') where id = 123;
It doesn't seem to provide any new functionality, just a minor difference in syntax.
- colanderman 9y agoEDIT: Turns out the below is not true, though you can achieve the same effect with the sub-select syntax. The row value can be a single value from some other query. (Rather than having to pick apart each column from the row value.) That said I think the feature has been there for a while, and now simply the "ROW" keyword is optionally allowed.
- walrus 9y agoThanks for the explanation! update comment set (modified, body) = (select now(), 'edited comment') where id = 123; You're right, it works the same without the `row` keyword in 9.6.
- netcraft 9y agoTIL too - works in 9.5 as well it seems: http://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=57e112a297ef86b5f1fa8ec44d3aa290 http://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=57e112a297ef86...
- colanderman 9y agoActually now that I re-read the grammar, sub-selects are yet another accepted syntax. Looks like the ROW syntax doesn't support row expressions like I thought. Not really sure the benefit beside making it easier to programmatically construct UPDATEs.
- mjw1007 9y agoIt's for when you want to update more than one column with values from the same record in another table, eg: UPDATE books SET (title, isbn) = ( SELECT title, isbn FROM other_books WHERE other_books.foo = books.bar); You can also do it with UPDATE ... FROM (which may be more efficient), but that's a PostgreSQL extension, while the added-in-9.5 syntax is SQL standard. UPDATE books SET title = other_books.title, isbn=other_books.isbn FROM other_books WHERE other_books.foo = books.bar;
- colanderman 9y agoThat's not the row syntax though, that's the sub-select syntax (my initial confusion). i.e., there are three options according to the grammar: SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } The second option, "row" syntax (to which the optional "ROW" keyword was recently added), doesn't allow for a row expression, only column expressions. The sub-select syntax suffices for row expressions (as your example demonstrates).
- walrus 9y agoHere's the commit where the "UPDATE .. SET ROW (col, ..) = (col, ..)" syntax was added as an alternative to "UPDATE .. SET (col, ..) = (col, ..)": https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=906bfcad7ba7cb3863fe0e2a7810be8e3cd84fbd https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit... I don't quite understand the commit message, but I think the new syntax resolves some ambiguity in the grammar in an edge case. From the diff of src/test/regress/sql/update.sql: -- *-expansion should work in this context: UPDATE update_test SET (a,b) = ROW(v.*) FROM (VALUES(21, 100)) AS v(i, j) WHERE update_test.a = v.i; -- you might expect this to work, but syntactically it's not a RowExpr: UPDATE update_test SET (a,b) = (v.*) FROM (VALUES(21, 101)) AS v(i, j) WHERE update_test.a = v.i;
- cryptonector 9y agoLooks like a purely cosmetic change. Scroll down to the bottom of https://www.postgresql.org/docs/10.0/static/sql-update.html https://www.postgresql.org/docs/10.0/static/sql-update.html to see the only related difference from https://www.postgresql.org/docs/9.6/static/sql-update.html https://www.postgresql.org/docs/9.6/static/sql-update.html .
- Twisell 9y agoI think it might also simplify the syntax if you want to update multiple columms from a composite type (which is essentially a ROW) so that writing sub query is not needed anymore.