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.
Add a comment
|
10 Answers
In version 9.3 and above you can do:
update users set flags = array_remove(flags, 'active')
Documentation on the function can be found here.
4 Comments
Joshua Burns
As of 2015, this is the most appropriate answer-- supposing you're running at least PostgreSQL 9.3.
Seldom 'Where's Monica' Needy
For those of you who haven't been able to roll up to version 9.3+ yet, you should be able to do write your own
array_remove() as shown: sqlfiddle.com/#!15/03d32/1/0
JMTyler
This is absolutely not "the most appropriate answer," as the question explicitly asks if we're able to "pop the last value." Even if you were to first query for the value of the last element, if that value was repeated at all in the array, all instances of it would be removed. That is obviously not the desired functionality of something like an
array_pop() function, which is what the OP is asking for.
aacotroneo
an answer that solves the question 'title', but not the detail being the most voted. That's because it aligns with how people use SO. Read the question title, then check the 1st answers to see if they solve 'your' problem, not the OPs! (OP should've used a better title)
The simplest way to remove last value:
array1 = array[1,2,3]
array1 = ( select array1[1:array_upper(array1, 1) - 1] )
1 Comment
Vidar
Yep, this is the answer - works for me fine with some semi-colons.
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
)
)
1 Comment
oggy
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 :)
There IS a SIMPLE way to remove a value from an array in PLAIN SQL:
SELECT unnest('{5,NULL,6}'::INT[]) EXCEPT SELECT NULL
it will remove all NULL values from array. Result will be:
#| integer |
------------
1| 5 |
2| 6 |
1 Comment
Seldom 'Where's Monica' Needy
Users of version 9.3+ should see this answer. Users of version 9.2 and below can wrap up the above code for reuse like this: sqlfiddle.com/#!15/03d32/1/0
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
2 Comments
oggy
Thanks, that would work though I guess slicing isn't exactly an efficient solution?
Matthew Wood
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.
Here is a function I use for integer[] arrays
CREATE OR REPLACE FUNCTION array_remove_item (array_in INTEGER[], item INTEGER)
RETURNS INTEGER[]
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT DISTINCT $1[s.i] AS "foo"
FROM GENERATE_SERIES(ARRAY_LOWER($1,1), ARRAY_UPPER($1,1)) AS s(i)
WHERE $2 != $1[s.i]
ORDER BY foo
);
$$;
This is obviously for integer arrays but could be modified for ANYARRAY ANYELEMENT
=> select array_remove_item(array[1,2,3,4,5], 3);
-[ RECORD 1 ]-----+----------
array_remove_item | {1,2,4,5}
Comments
I've created a array_pop function so you can remove an element with known value from an array.
CREATE OR REPLACE FUNCTION array_pop(a anyarray, element character varying)
RETURNS anyarray
LANGUAGE plpgsql
AS $function$
DECLARE
result a%TYPE;
BEGIN
SELECT ARRAY(
SELECT b.e FROM (SELECT unnest(a)) AS b(e) WHERE b.e <> element) INTO result;
RETURN result;
END;
$function$
there is also a gist version https://gist.github.com/1392734
Comments
I'm running on 9.2 and I'm able to execute this:
update tablename set arrcolumn=arrcolumn[1:array_length(arrcolumn)-1];
or you can shift off the front element with the same kind of thing:
update tablename set arrcolumn=arrcolumn[2:array_length(arrcolumn)];
Careful, programmers -- for some reason still unknown to science, pgsql arrays are 1-indexed instead of 0-indexed.
Comments
Try this:
update table_name set column_name=column_name[1:array_upper(column_name, 1)-1];
Comments
My function for all types of arrays.
Outer function:
CREATE OR REPLACE FUNCTION "outer_array"(anyarray, anyarray) RETURNS anyarray AS $$
SELECT
"new"."item"
FROM (
SELECT
ARRAY(
SELECT
"arr"."value"
FROM (
SELECT
generate_series(1, array_length($1, 1)) AS "i",
unnest($1) AS "value"
) "arr"
WHERE
"arr"."value" <> ALL ($2)
ORDER BY
"arr"."i"
) AS "item"
) "new"
$$
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
;
Inner function:
CREATE OR REPLACE FUNCTION "inner_array"(anyarray, anyarray) RETURNS anyarray AS $$
SELECT
"new"."item"
FROM (
SELECT
ARRAY(
SELECT
"arr"."value"
FROM (
SELECT
generate_series(1, array_length($1, 1)) AS "i",
unnest($1) AS "value"
) "arr"
WHERE
"arr"."value" = ANY ($2)
ORDER BY
"arr"."i"
) AS "item"
) "new"
$$
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
;
