Wednesday, 19 April 2017

Remove multidimensional element from array in Postgres

I have a table called listings where each record has a multi-dimensional array called images. Each element contains a [base64 string, filename].

If a user deletes a image from their listing, I want to do a query where the listing.id equals the the listing id and listing.posted_by equals the user id, then delete that element from the array images.

To start with, I tried:

UPDATE listings SET images = array_remove(images, '3/4/image-3-4-1492539133313.jpeg') WHERE listings.id = 4;

but I got ERROR: removing elements from multidimensional arrays is not supported from:

id               | 4
created_at       | 2017-04-18 18:12:11.983523+00
posted_by        | 10209280753550922

images           | 

How do I go about removing a complete multi-dimensional element from a multi-dimensional array? I want to make sure it shortens the array and not leaves a null in it's place.



via dman

No comments:

Post a Comment