By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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}')
, (2 , '{A,B,X,B,Y,B}'); -- to actually see the 1st one deleted
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';
2 rows affected
SELECT *, f_array_remove_elem1(test_array, 'B')
FROM test_table;
| id | test_array | f_array_remove_elem1 |
|---|---|---|
| 1 | {A,A,A,B,B,B} | {A,A,A,B,B} |
| 2 | {A,B,X,B,Y,B} | {A,X,B,Y,B} |
UPDATE test_table
SET test_array = f_array_remove_elem1(test_array, 'B')
WHERE id = 1 -- only id 1
RETURNING *;
| id | test_array |
|---|---|
| 1 | {A,A,A,B,B} |