1350

update table with 4 columns specified, but only 2 columns are available

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?

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.

Recommend

  • How to make ftp transfer of multiple files in directory (to remote) using find command output
  • Why is Matlab's inv slow and inaccurate?
  • In Python, how can I raise a square matrix represented as a numpy.ndarray to non-integer powers?
  • Using extern @class in order to add a category?
  • Replace and retrieve placeholder value
  • Getting error java.io.FileNotFoundException (log4j log file) at the time of publish project on cloud
  • Setting the run time properties on SpringApplicationBuilder()
  • Security issues with PHP's Readfile method
  • Grails calculated field in SQL
  • onBackPressed() not being executed
  • d3 v4 drag and drop with TypeScript
  • Swift: Switch statement fallthrough behavior
  • Word Open XML Mail Merge
  • Meteor: Do Something On Email Verification Confirmation
  • why do I get the error when installing the gem 'pg'? [duplicate]
  • SignalR .NET Client Invoke throws an exception
  • Using variable in a value field in jMeter
  • Using $this when not in object context
  • java.lang.NoClassDefFoundError: com.parse.Parse$Configuration$Builder on below Lollipop versions
  • Array.prototype.includes - not transformed with babel
  • Volley JsonObjectRequest send headers in GET Request
  • Accessing IRQ description array within a module and displaying action names
  • How to add date and time under each post in guestbook in google app engine
  • JSON with duplicate key names losing information when parsed
  • Which linear programming package should I use for high numbers of constraints and “warm starts” [clo
  • Can a Chrome extension content script make an jQuery AJAX request for an html file that is itself a
  • Build own AppleScript numerical error handling
  • Websockets service method fails during R startup
  • Google cloud sdk not working when python points python3
  • Delete MySQLi record without showing the id in the URL
  • Return words with double consecutive letters
  • Is there a mandatory requirement to switch app.yaml?
  • Hits per day in Google Big Query
  • Understanding cpu registers
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Linking SubReports Without LinkChild/LinkMaster
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Programmatically clearing map cache
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices