Postgres 14

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?.

-- Postgres 14 for text[] only (a bit faster)
CREATE FUNCTION f_arr_except_arr_pg14(a1 text[], a2 text[])
  RETURNS text[]
  LANGUAGE SQL IMMUTABLE PARALLEL SAFE
BEGIN ATOMIC
SELECT ARRAY (SELECT unnest(a1) EXCEPT ALL SELECT unnest(a2));
END;

-- Any Postgres version for any element type
CREATE OR REPLACE FUNCTION f_arr_except_arr(a1 anyarray, a2 anyarray)
  RETURNS anyarray
  LANGUAGE SQL IMMUTABLE PARALLEL SAFE AS
$func$
SELECT ARRAY (SELECT unnest(a1) EXCEPT ALL SELECT unnest(a2));
$func$;

CREATE FUNCTION

CREATE FUNCTION

-- f_arr_except_arr_pg14() only for text[]
SELECT f_arr_except_arr_pg14('{string1,string2,string3,null,null}'::text[], '{string1,string2,null}');

f_arr_except_arr_pg14

{NULL,string3}

SELECT 1

-- f_arr_except_arr()
SELECT f_arr_except_arr('{string1,string2,string3,null,null}'::text[], '{string1,string2,null}');
SELECT f_arr_except_arr('{1,2,3,null,5,null}'::real[], '{1,2,null}');

f_arr_except_arr

{NULL,string3}

SELECT 1

f_arr_except_arr

{NULL,3,5}

SELECT 1