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

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




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

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

Search: