Waiting for 9.5 – Implement UPDATE tab SET (col1,col2,…) = (SELECT …), …

On 18th of June, Tom Lane committed patch:

Implement UPDATE tab SET (col1,col2,...) = (SELECT ...), ...
 
This SQL-standard feature allows a sub-SELECT yielding multiple columns
(but only one row) to be used to compute the new values of several columns
to be updated.  While the same results can be had with an independent
sub-SELECT per column, such a workaround can require a great deal of
duplicated computation.
 
The standard actually says that the source for a multi-column assignment
could be any row-valued expression.  The implementation used here is
tightly tied to our existing sub-SELECT support and can't handle other
cases; the Bison grammar would have some issues with them too.  However,
I don't feel too bad about this since other cases can be converted into
sub-SELECTs.  For instance, "SET (a,b,c) = row_valued_function(x)" could
be written "SET (a,b,c) = (SELECT * FROM row_valued_function(x))".

The commit message explains everything nicely, but let me just show it, with one additional info.

For starters – you could have used:

UPDATE TABLE SET (a,b,c) = (...)

for quite some time now, for example, in 9.1:

$ CREATE TABLE test AS SELECT oid, NULL::text AS database_name, NULL::int4 AS encoding FROM pg_database;
SELECT 5
 
$ UPDATE test AS t SET (database_name, encoding) = (x.datname, x.encoding) FROM pg_database x WHERE x.oid = t.oid;
UPDATE 5
 
$ SELECT * FROM test WHERE database_name ~ 'templ';
  oid  | database_name | encoding 
-------+---------------+----------
     1 | template1     |        0
 11910 | template0     |        0
(2 ROWS)

The new feature in here is that you can use explicit subquery, which previously would have failed:

$ CREATE TABLE test AS SELECT oid, NULL::text AS database_name, NULL::int4 AS encoding FROM pg_database;
SELECT 5
 
$ UPDATE test AS t SET (database_name, encoding) = (SELECT x.datname, x.encoding FROM pg_database x WHERE x.oid = t.oid );
ERROR:  syntax error at OR near "select"
LINE 1: UPDATE test AS t SET (database_name, encoding) = (SELECT x.d...
                                                          ^

and now, in 9.5devel:

$ CREATE TABLE test AS SELECT oid, NULL::text AS database_name, NULL::int4 AS encoding FROM pg_database;
SELECT 5
 
$ UPDATE test AS t SET (database_name, encoding) = (SELECT x.datname, x.encoding FROM pg_database x WHERE x.oid = t.oid );
UPDATE 5

This will be specifically useful with functions, as described in the commit message.

For example, let's assume we have a function, which returns, for every integer – it's 2nd and third power:

CREATE OR REPLACE FUNCTION powers( IN INPUT INT4, OUT square INT8, OUT cube INT8 ) RETURNS record AS $$
BEGIN
    square := INPUT * INPUT;
    cube := square * INPUT;
    RETURN;
END;
$$ LANGUAGE plpgsql;

Now, we have simple table with some integers:

$ CREATE TABLE test (base int4, p2 int8, p3 int8);
CREATE TABLE
 
$ INSERT INTO test (base) SELECT generate_series(2,15, 3);
INSERT 0 5

Previously, we'd have to write something like this:

UPDATE test AS t
    SET p2 = (t2.powers).square,
        p3 = (t2.powers).cube
FROM (
    SELECT base, powers(base)
    FROM test
) AS t2
WHERE t2.base = t.base;

Now, the query is much simpler and easier to read:

UPDATE test AS t
    SET (p2, p3) = (SELECT square, cube FROM powers(t.base))

It's somehow similar to LATERAL.

4 thoughts on “Waiting for 9.5 – Implement UPDATE tab SET (col1,col2,…) = (SELECT …), …”

  1. Is it possible to use constants in subselect? For instance

    update tab1 set (c1, c2) = (select 7, t2.name from t2)

  2. @Vano:
    of course – it’s just normal subselect. The only thing that is important is that it should return just one row.

  3. Hi Depesz. I have a off-topic question. Do you know if there are any plans to add mysql like inserts which looks similar to updates?

    insert into tab set
    c1 = ‘aa’,
    c2 = 10

    I must admit that form is very convenient and greatly simplifies creating dynamic queries.

Comments are closed.