Apply division to each element in column of type array in PostgreSQL

Question

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?

Juan Carlos Oropeza · Accepted Answer · 2016-12-16 18:52:49Z

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;

answered Dec 16, 2016 at 18:52

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kostas Over a year ago

Thanks, that works! Could you please edit your post to answer how you would insert it to a new column of type float[4]?

Juan Carlos Oropeza Over a year ago

You need array_agg() stackoverflow.com/questions/6402043/…

Collectives™ on Stack Overflow

Apply division to each element in column of type array in PostgreSQL

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related