Update or Insert (multiple rows and columns) from subquery in PostgreSQL

Question

I'm trying to do something like this in Postgres:

UPDATE table1 SET (col1, col2) = (SELECT col2, col3 FROM othertable WHERE othertable.col1 = 123);

INSERT INTO table1 (col1, col2) VALUES (SELECT col1, col2 FROM othertable)

But point 1 is not possible even with Postgres 9.0 as mentioned in the docs (http://www.postgresql.org/docs/9.0/static/sql-update.html)

Also, point 2 seems not working. I'm getting the following error: the subquery must return only one column.

I hope somebody has a workaround for me. otherwise, the queries will take a lot of time :(.

FYI: I'm trying to select different columns from several tables and store them into a temporary table so that another application can easily fetch the prepared data.

Answer 1

Use the following query for the UPDATE:

UPDATE table1 

   SET col1 = othertable.col2,

       col2 = othertable.col3 

  FROM othertable 

 WHERE othertable.col1 = 123;

Use the below query for the INSERT:

INSERT INTO table1 (col1, col2) 

SELECT col1, col2 

  FROM othertable

Note:  If you are using the SELECT statement to populate the INSERT values then you don't need the VALUES syntax.