从数组中删除一个非唯一值

Question

假设在PostgreSQL 9.6中有以下表格：

CREATE TABLE test_table (
   id int PRIMARY KEY
 , test_array text[]
);

使用以下这一行代码：

INSERT INTO test_table (id, test_array)
VALUES (1 , '{A,A,A,B,B,B}');

我该如何删除一个'B'值？

我不能使用以下方法：

UPDATE test_table
SET test_array = array_remove(test_array, 'B')
WHERE id = 1;

由于它删除了值为'B'的所有元素。我只想删除一个单独的元素（比如第一个）。

有什么好的想法吗？

- R. StackUser

---

请参考DBA SE上的这个问题：https://dba.stackexchange.com/questions/94639/delete-array-element-by-index - Sharon Ben Asher

假设我知道字符串的确切索引。我需要一个函数，可以输入该字符串，它将找到包含该字符串的第一个索引，然后从数组中删除该索引。 - R. StackUser

你只需要将 "position('B'" 中的 'B' 替换为你想要替换的字符/字符串。就像在你的示例函数 array_remove(test_array, 'B') 中传递 'B' 一样。 - Fact

Answer 1

我在扩展这个页面提供的信息后找到了答案（由Sharon Ben Asher建议） https://dba.stackexchange.com/questions/94639/delete-array-element-by-index

创建那里提到的函数：

"最初的回答"

CREATE OR REPLACE FUNCTION f_array_remove_elem(anyarray, int)
RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[1:$2-1] || $1[$2+1:2147483647]';

并在此基础上实现array_position函数，这是示例的最终语句：

UPDATE test_table SET test_array = f_array_remove_elem(test_array, array_position(test_array, 'B')) WHERE id = 1;

- R. StackUser

---

附言：为什么要进行转换test_array::text[]。列已经是text[]了，不是吗？ - Erwin Brandstetter

已经移除了不必要的子查询和转换，感谢您的帮助。 - R. StackUser

我购买了一个流浪的 SELECT。 - Erwin Brandstetter

Answer 2

根据您在dba.SE上找到并成功使用的我的旧回答：

• 按索引删除数组元素

您可以进一步采取以下措施：

Original Answer翻译成"最初的回答"

CREATE OR REPLACE FUNCTION f_array_remove_elem1(anyarray, anyelement)
  RETURNS anyarray LANGUAGE sql IMMUTABLE AS
'SELECT $1[:idx-1] || $1[idx+1:] FROM array_position($1, $2) idx';

这个函数的第二个参数是要移除元素的值。使用多态伪类型anyelement使其适用于任何数组类型。

然后UPDATE就是：

Original Answer翻译成"最初的回答"

UPDATE test_table
SET    test_array = f_array_remove_elem1(test_array, 'B')
WHERE  id = 1;

db<>fiddle 这里

如果使用我最初的函数f_array_remove_elem()，该函数使用索引位置而不是元素值，您可以避免使用子查询：

UPDATE test_table
SET    test_array = f_array_remove_elem(test_array, array_position(test_array, 'B'))
WHERE  id = 1;

可能甚至比我的新函数快一点。
请注意，我旧答案底部的简化版本适用于Postgres 9.6。

- Erwin Brandstetter

Answer 3

你可以试试这个。

select SUBSTRING ( '{A,A,A,B,B,B}',1,position('B' in '{A,A,A,B,B,B}') -1)||SUBSTRING ('{A,A,A,B,B,B}',position('B' in '{A,A,A,B,B,B}')+1)

首先在字符串中找到B的位置，然后获取从第一个B之前的子字符串，即'{A,A,A}'，然后获取剩余字符串中留下B的子字符串，即',B,B}'

- Fact