Is there a way to remove a value from an array in pgSQL? Or to be more precise, to pop the last value? Judging by this list the answer seems to be no. I can get the result I want with an additional index pointer, but it's a bit cumbersome.
+1
A:
No, I don't think you can. At least not without writing something ugly like:
SELECT ARRAY (
SELECT UNNEST(yourarray) LIMIT (
SELECT array_upper(yourarray, 1) - 1
)
)
Magnus Hagander
2010-01-15 17:58:18
Judging from what Google tells me, it seems that I can't. I'll mark this as the accepted answer unless somebody proves you wrong :)
oggy
2010-01-15 20:10:37
+1
A:
I'm not sure about your context, but this should give you something to work with:
CREATE TABLE test (x INT[]);
INSERT INTO test VALUES ('{1,2,3,4,5}');
SELECT x AS array_pre_pop,
x[array_lower(x,1) : array_upper(x,1)-1] AS array_post_pop,
x[array_upper(x,1)] AS popped_value
FROM test;
array_pre_pop | array_post_pop | popped_value
---------------+----------------+--------------
{1,2,3,4,5} | {1,2,3,4} | 5
Matthew Wood
2010-01-15 18:15:31
Thanks, that would work though I guess slicing isn't exactly an efficient solution?
oggy
2010-01-15 18:20:41
I think it's your best method considering there's no built-in function for pop(). Without knowing the specifics, I can't give better advice. If you want to loop through the contents for a particular record, then unnest() would probably be better as it would convert into a set of records. However, if you just want to update a table to remove all the "last elements" of the array in multiple records, array slicing would be the way to go.
Matthew Wood
2010-01-15 18:29:27