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:
table "temp" has 2 columns available but 4 columns specified
Whats the problem, and how can i solve it?Answer1:
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.