3 ms·

Thanks for the explanation! update comment set (modified, body) = (select now(), 'edited comment') where id = 123; You're right, it works the same without t

by walrus 9y ago

Thanks 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 ago
TIL 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 ago
Actually 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 ago
  It'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 ago
    That'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 ago
  Here'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;

by walrus 9y ago

Thanks 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 ago

TIL 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 ago

Actually 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 ago
It'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 ago
  That'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 ago
Here'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;