unnesting in a UPDATE/SET statement, PostgreSQL/PHP

Question

I have a table like the following:

point_path_id     line_path_id     season_id     gps_time     heading     roll     pitch       geometry
    1                 ___             ___           ___         ___        ___      ___       PostGISGeom
    2                 ___             ___           ___         ___        ___      ___       PostGISGeom
    3                 ___             ___           ___         ___        ___      ___       PostGISGeom
    4                 ___             ___           ___         ___        ___      ___       PostGISGeom  
    5                 ___             ___           ___         ___        ___      ___       PostGISGeom       

I also, in PHP, have the list of ID's I want to update and values for line_path_id and season_id. I also have arrays for gps_time,heading,roll,pitch.

I have the following SQL statement (echo from PHP)

UPDATE greenland.point_paths 

SET 

line_path_id=1,
season_id=2, 
gps_time=unnest(array[1303475178.0031,1303475178.0081,1303475179.0081,1303475180.0081,1303475181.0081]::double precision[]),
heading=unnest(array[-2.0819464,-2.0819407,-2.0820324,-2.08202,-2.0819855]::double precision[]),
roll=unnest(array[-0.007395,-0.007395,-0.0073832,-0.0073949,-0.0073853]::double precision[]),
pitch=unnest(array[-0.0246114,-0.0246115,-0.0246108,-0.024582,-0.0245905]::double precision[]) 

WHERE point_path_id IN (1,2,3,4,5);

This is inserting, but not correctly. The first value of the arrays is inserted for all 5 id's in point_path_id. I get the following in the table:

This unnesting works fine in many other statements, but I cannot seem to get it to work correctly in this form. Where is my mistake?

HERE IS SOME BACKGROUND ON WHAT IS HAPPENING BEFORE THIS: A JSON string of data is sent via URLEAD(MATLAB) and decoded/inserted into many tables via PHP.

A PostGIS linestring GEOM is inserted and it's ID returned (line_path_id) A Season is inserted and it's ID returned (season_id)

Then the linestring is dumped into points in the table described above and the serial point_path_id auto-generated and returned.

Then the data associated with each point needs to be inserted (this is what i'm trying to do)

I have the line_path_id, season_id, a gps_time/heading/roll/pitch for each point. The time/heading/roll/pitch are in arrays the length of the number of points.

There can be 100,000+ points, but lets test with say 5.

After the points are dumped there is a table shown again here:

point_path_id     line_path_id     season_id     gps_time     heading     roll     pitch       geometry
    1                 ___             ___           ___         ___        ___      ___       PostGISGeom
    2                 ___             ___           ___         ___        ___      ___       PostGISGeom
    3                 ___             ___           ___         ___        ___      ___       PostGISGeom
    4                 ___             ___           ___         ___        ___      ___       PostGISGeom  
    5                 ___             ___           ___         ___        ___      ___       PostGISGeom   

I also have two variables: $line_path_id = # $season_id = #

And 4 arrays $gps_time = [#,#,#,#,#]; heading... roll... pitch...

I need to insert the associated values for each point geometry. This is the goal.

Hope this helps find a best solution.

Answer 1

unnest() is a set returning function. Your problem is that the each SET assignment in the UPDATE statement is looking for a single value to assign to the field of each row found by the WHERE clause. The UPDATE does not know that you intend the first array value to go to the row with point_path_id=1 and so on.

For small arrays, you could rewrite your query as:

UPDATE greenland.point_paths 
   SET line_path_id=1,
       season_id=2, 
       gps_time=(array[1303475178.0031,1303475178.0081,1303475179.0081,1303475180.0081,1303475181.0081])[point_path_id]::double precision[],
       heading=(array[-2.0819464,-2.0819407,-2.0820324,-2.08202,-2.0819855])[point_path_id]::double precision[],
       roll=(array[-0.007395,-0.007395,-0.0073832,-0.0073949,-0.0073853])[point_path_id]::double precision[],
       pitch=(array[-0.0246114,-0.0246115,-0.0246108,-0.024582,-0.0245905])[point_path_id]::double precision[]
 WHERE point_path_id IN (1,2,3,4,5);

Though that is relying on point_path_id matching the array indices.

You probably need a different approach to getting this data into the system. If you are expecting to be updating 100,000 rows at a time in this table, perhaps you should do a bulk upload of the data with COPY into a staging table that has point_path_id, season_id, line_path, gps_time, roll, pitch, heading each as a single value per row. Then you could do:

UPDATE greenland.points_path pp
   SET season_id=s.season_id,
       line_path=s.line_path,
       gps_time=s.gps_time,
       roll=s.roll,
       pitch=s.pitch,
       heading=s.heading
  FROM staging s
 WHERE pp.point_path_id=s.point_path_id