Question:

I have one table called test, which has 4 columns:

id INT v_out INT v_in INT label CHARACTER

I'm trying to update the table with the following query:

String sql = " update test set v_out = temp.outV , v_in = temp.inV , label = temp.label from ( values( (1,234,235,'abc') ,(2,234,5585,'def') ) ) as temp (e_id, outV, inV, label) where id = temp.e_id ";

When I execute it, I got the error:

org.postgresql.util.PSQLException: ERROR:

table "temp" has 2 columns available but 4 columns specified

Whats the problem, and how can i solve it?

The values for the values clause must not be enclosed in parentheses:

values ( (1,234,235,'abc'), (2,234,5585,'def') )

creates a single row with two columns. Each column being an anonymous "record" with 4 fields.

What you want is:

from ( values (1,234,235,'abc'), (2,234,5585,'def') ) as temp (e_id, outV, inV, label)

SQLFiddle showing the difference: <a href="http://sqlfiddle.com/#!15/d41d8/2763" rel="nofollow">http://sqlfiddle.com/#!15/d41d8/2763</a>

This behavior is documented, but that is quite hard to find: <br /><a href="http://www.postgresql.org/docs/current/static/rowtypes.html#AEN7362" rel="nofollow">http://www.postgresql.org/docs/current/static/rowtypes.html#AEN7362</a>

It's essentially the same thing as select (col1, col2) from some_table vs. select col1, col2 from some_table. The first one returns one column with an anonymous composite type that has two fields. The second one returns two columns from the table.