Postgres 9.6

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}