postgresql 删除 pgSQL 中的数组值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2072776/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove array values in pgSQL
提问by oggy
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 listthe answer seems to be no. I can get the result I want with an additional index pointer, but it's a bit cumbersome.
有没有办法从 pgSQL 中的数组中删除一个值？或者更准确地说，弹出最后一个值？从这份清单来看，答案似乎是否定的。我可以用一个额外的索引指针得到我想要的结果，但是有点麻烦。
采纳答案by Magnus Hagander
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
)
)
回答by John Sheehan
In version 9.3 and above you can do:
在 9.3 及更高版本中，您可以执行以下操作：
update users set flags = array_remove(flags, 'active')
回答by luke
The simplest way to remove last value:
删除最后一个值的最简单方法：
array1 = array[1,2,3]
array1 = ( select array1[1:array_upper(array1, 1) - 1] )
回答by Matthew Wood
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
回答by user2210877
There IS a SIMPLE way to remove a value from an array in PLAIN SQL:
有一种简单的方法可以从 PLAIN SQL 中的数组中删除一个值：
SELECT unnest('{5,NULL,6}'::INT[]) EXCEPT SELECT NULL
it will remove all NULL values from array. Result will be:
它将从数组中删除所有 NULL 值。结果将是：
#| integer |
------------
1| 5 |
2| 6 |
回答by Dustin Butler
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 [s.i] AS "foo"
FROM GENERATE_SERIES(ARRAY_LOWER(,1), ARRAY_UPPER(,1)) AS s(i)
WHERE != [s.i]
ORDER BY foo
);
$$;
This is obviously for integer arrays but could be modified for ANYARRAY ANYELEMENT
这显然适用于整数数组，但可以修改为 ANYARRAY ANYELEMENT
=> select array_remove_item(array[1,2,3,4,5], 3);
-[ RECORD 1 ]-----+----------
array_remove_item | {1,2,4,5}
回答by amwinter
I'm running on 9.2 and I'm able to execute this:
我在 9.2 上运行，我能够执行这个：
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.
小心，程序员——出于某种科学仍然未知的原因，pgsql 数组是 1-indexed 而不是 0-indexed。
回答by number5
I've created a array_pop function so you can remove an element with known value from an array.
我创建了一个 array_pop 函数，因此您可以从数组中删除具有已知值的元素。
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
还有一个要点版本https://gist.github.com/1392734
回答by Karim Tabet
Try this:
尝试这个：
update table_name set column_name=column_name[1:array_upper(column_name, 1)-1];
update table_name set column_name=column_name[1:array_upper(column_name, 1)-1];
回答by Roman V. NIkolaev
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)) AS "i",
unnest() AS "value"
) "arr"
WHERE
"arr"."value" <> ALL ()
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)) AS "i",
unnest() AS "value"
) "arr"
WHERE
"arr"."value" = ANY ()
ORDER BY
"arr"."i"
) AS "item"
) "new"
$$
LANGUAGE sql
IMMUTABLE
RETURNS NULL ON NULL INPUT
;
