0

I have a column box_coordinates of type integer[4] and I want to divide each element of the array by 640 and insert it into a new column of type float[4]. I first try to divide each element of the array before aggregating

SELECT n/640::float
FROM (
    SELECT unnest(box_coordinates) 
    FROM images 
) AS n;

but it fails with error

ERROR:  operator does not exist: record / integer
HINT:   No operator matches the given name and argument type(s). 
        You might need to add explicit type casts.

How do I apply the array element division and then insert the result into a new column of type float[4], while keeping the order of the elements in the new array unchanged?

Kostas
  • 380
  • 5
  • 13

1 Answers1

2

you are using n instead unnest result

SELECT n.coord/640::float
FROM (
    SELECT unnest(box_coordinates) as coord
    FROM images 
    LIMIT 4
) AS n;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • Thanks, that works! Could you please edit your post to answer how you would insert it to a new column of type float[4]? – Kostas Dec 16 '16 at 18:59
  • You need `array_agg()` http://stackoverflow.com/questions/6402043/store-select-querys-output-in-one-array-in-postgres – Juan Carlos Oropeza Dec 16 '16 at 19:24