Postgres 13

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 t(x integer[], y integer[]);
insert into t values
('{10}',      '{10}'),
('{6}',       '{5,6}'),
('{4,5,6}',   '{5,6}'),
('{5,6}',     '{5,6}'),
('{7,8}',     '{11}'),
('{11}',      '{5,6,7}'),
('{11}',      '{8}'), 
('{9,10}',    '{6}'),

--
-- records below added for testing!
--

('{11}',      '{5,8,10,11,133}'),  
('{9,10}',    '{4,5,6,8,9,10,11}'),
('{9,10}',    '{4,5,6,8,9,10,11}');



11 rows affected

--
-- in answer
--

SELECT
  t.x,
  CASE                             -- CASE because I can't use the simple
    WHEN NOT t.x && t.y THEN t.y   -- '-' (minus) operator of INTARRAY
    WHEN t.x @> t.y THEN '{}'
    ELSE '{400000}'                -- the '{40000}' is a placeholder
  END AS y
FROM t;


x
y


{10}
{}

{6}
{400000}

{4,5,6}
{}

{5,6}
{}

{7,8}
{11}

{11}
{5,6,7}

{11}
{8}

{9,10}
{6}

{11}
{400000}

{9,10}
{400000}

{9,10}
{400000}


--
-- not in answer - what happens without ROW_NUMBER()...
--

SELECT
  t.x,
  CASE
    WHEN NOT t.x && t.y THEN t.y
    WHEN t.x @> t.y THEN '{}'
    ELSE 
    (
      ARRAY
    
      (SELECT
        -- ROW_NUMBER() OVER () AS rn,
        UNNEST(t.y)
      FROM t
    INTERSECT
    SELECT
      -- ROW_NUMBER() OVER () AS rn,
      UNNEST(t.x)
      FROM t )
    )
  END AS y
FROM t;


x
y


{10}
{}

{6}
{10,5,4,6,11,9,8,7}

{4,5,6}
{}

{5,6}
{}

{7,8}
{11}

{11}
{5,6,7}

{11}
{8}

{9,10}
{6}

{11}
{10,5,4,6,11,9,8,7}

{9,10}
{10,5,4,6,11,9,8,7}

{9,10}
{10,5,4,6,11,9,8,7}


--
-- in answer, but only works for one row!
--

SELECT ARRAY
(
  SELECT
    unn 
  FROM
  (
    SELECT
      ROW_NUMBER() OVER () AS rn,
      UNNEST(t.y) AS unn
    FROM t
    EXCEPT
    SELECT
      ROW_NUMBER() OVER (),
      UNNEST(t.x)
    FROM t ) AS tx
    WHERE rn = 2
);


array


{5}


--
-- in answer - shows CASE with ARRAY with EXCEPT
--

SELECT
  t.x,
  CASE
    WHEN NOT t.x && t.y THEN t.y
    WHEN t.x @> t.y THEN '{}'
    ELSE 
    (
      ARRAY
      (
        SELECT
          un 
        FROM
        (
          SELECT
            ROW_NUMBER() OVER () AS rn,
            UNNEST(t.y) AS un
          FROM t
          EXCEPT
          SELECT
            ROW_NUMBER() OVER () AS rn,
            UNNEST(t.x)
          FROM t 
        ) AS tx
        WHERE rn = 2
     )
   ) END AS y
FROM t;


x
y


{10}
{}

{6}
{5}

{4,5,6}
{}

{5,6}
{}

{7,8}
{11}

{11}
{5,6,7}

{11}
{8}

{9,10}
{6}

{11}
{5}

{9,10}
{5}

{9,10}
{5}


WITH cte AS
(
  SELECT
    ROW_NUMBER() OVER () AS rnc,
    t.x AS cx, t.y AS cy
  FROM t
)
SELECT
   t.x,
  CASE
    WHEN NOT t.x && t.y THEN t.y
    WHEN t.x @> t.y THEN '{}'
    ELSE 
    (
      ARRAY
      (
        SELECT
          un 
        FROM
        (
          SELECT
            ROW_NUMBER() OVER () AS rn,
            UNNEST(t.y) AS un
          FROM t
          EXCEPT
          SELECT
            ROW_NUMBER() OVER () AS rn,
            UNNEST(t.x)
          FROM t 
        ) AS tx
        WHERE rn = (SELECT c.rnc FROM cte c WHERE c.cx = t.x AND c.cy = t.y LIMIT 1)
     )
   ) END AS y
FROM t;


x
y


{10}
{}

{6}
{5}

{4,5,6}
{}

{5,6}
{}

{7,8}
{11}

{11}
{5,6,7}

{11}
{8}

{9,10}
{6}

{11}
{5,10,133,8}

{9,10}
{6,4,8,5,11}

{9,10}
{6,4,8,5,11}


WITH cte2 AS
(
WITH cte AS
(
  SELECT
    ROW_NUMBER() OVER () AS rnc,  -- rn cte
    t.x AS cx, t.y AS cy
  FROM t
)
SELECT
   ROW_NUMBER() OVER () AS rnf, -- rn final
   t.x,
   t.y AS old_y,  -- needed for UPDATE...

  CASE
    WHEN NOT t.x && t.y THEN t.y
    WHEN t.x @> t.y THEN '{}'
    ELSE 
    (
      ARRAY
      (
        SELECT
          un 
        FROM
        (
          SELECT
            ROW_NUMBER() OVER () AS rn,
            UNNEST(t.y) AS un
          FROM t
          EXCEPT
          SELECT
            ROW_NUMBER() OVER () AS rn,
            UNNEST(t.x)
          FROM t 
        ) AS tx
        WHERE rn = (SELECT c.rnc FROM cte c WHERE c.cx = t.x AND c.cy = t.y LIMIT 1)
     )
   ) END AS y
FROM t
)
SELECT * FROM cte2;


rnf
x
old_y
y


1
{10}
{10}
{}

2
{6}
{5,6}
{5}

3
{4,5,6}
{5,6}
{}

4
{5,6}
{5,6}
{}

5
{7,8}
{11}
{11}

6
{11}
{5,6,7}
{5,6,7}

7
{11}
{8}
{8}

8
{9,10}
{6}
{6}

9
{11}
{5,8,10,11,133}
{5,10,133,8}

10
{9,10}
{4,5,6,8,9,10,11}
{6,4,8,5,11}

11
{9,10}
{4,5,6,8,9,10,11}
{6,4,8,5,11}


-- 
-- from https://stackoverflow.com/questions/36908495/update-with-result-from-cte-postgresql
--

UPDATE job
SET jobdate = cte.date
FROM cte
WHERE job.jobid = cte.jobid
ERROR:  relation "job" does not exist
LINE 5: UPDATE job
               ^


SELECT * FROM t;


x
y


{10}
{10}

{6}
{5,6}

{4,5,6}
{5,6}

{5,6}
{5,6}

{7,8}
{11}

{11}
{5,6,7}

{11}
{8}

{9,10}
{6}

{11}
{5,8,10,11,133}

{9,10}
{4,5,6,8,9,10,11}

{9,10}
{4,5,6,8,9,10,11}
x	y
{10}	{}
{6}	{400000}
{4,5,6}	{}
{5,6}	{}
{7,8}	{11}
{11}	{5,6,7}
{11}	{8}
{9,10}	{6}
{11}	{400000}
{9,10}	{400000}
{9,10}	{400000}
x	y
{10}	{}
{6}	{10,5,4,6,11,9,8,7}
{4,5,6}	{}
{5,6}	{}
{7,8}	{11}
{11}	{5,6,7}
{11}	{8}
{9,10}	{6}
{11}	{10,5,4,6,11,9,8,7}
{9,10}	{10,5,4,6,11,9,8,7}
{9,10}	{10,5,4,6,11,9,8,7}