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

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

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: subquery must return only one column.

Hope somebody has a workaround for me. otherwise the queries will take a looot 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.

Solution

For the UPDATE

Use:

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

For the INSERT

Use:

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

You don't need the VALUES syntax if you are using a SELECT to populate the INSERT values.