Hacker News new | past | comments | ask | show | jobs | submit login

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.



EDIT: 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.


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.


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.


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...

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;


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;


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).


TIL too - works in 9.5 as well it seems: http://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=57e112a297ef86...


Looks like a purely cosmetic change. Scroll down to the bottom of 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 .


I 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.




Registration is open for Startup School 2019. Classes start July 22nd.

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: