add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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?.
3 rows affected
3 rows affected
5 rows affected
mixture_id chem_ids
1 {1}
3 {1,2}
2 {2,3}
1 rows affected
mixture_id chem_ids
1 {1}
3 {1,2}
2 {1,3}
ERROR:  duplicate key value violates unique constraint "mixtures_chem_ids_key"
DETAIL:  Key (chem_ids)=({1}) already exists.
CONTEXT:  SQL statement "UPDATE mixtures AS m
   SET    chem_ids = sort(COALESCE(m.chem_ids, '{}')
                        - COALESCE(o.chem_ids, '{}')
                        + COALESCE(n.chem_ids, '{}'))
   FROM  (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   new_table
      GROUP  BY 1
      ) n
   FULL  JOIN (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   old_table
      GROUP  BY 1
      ) o USING (mixture_id)
   WHERE m.mixture_id = COALESCE(n.mixture_id, o.mixture_id)
   AND   m.chem_ids IS DISTINCT FROM sort(COALESCE(m.chem_ids, '{}')
                                        - COALESCE(o.chem_ids, '{}')
                                        + COALESCE(n.chem_ids, '{}'))"
PL/pgSQL function trg_mixture_chems_upaft() line 3 at SQL statement

2 rows affected
mixture_id chem_ids
3 {1,2}
1 {1,1,3}
2 {}
1 rows affected
mixture_id chem_ids
3 {1,2}
2 {3}
1 {1}
1 rows affected
mixture_id chem_ids
3 {1,2}
1 {1,3}
2 {}
2 rows affected
mixture_id chem_ids
3 {1,2}
2 {}
1 {3}
ERROR:  duplicate key value violates unique constraint "mixtures_chem_ids_key"
DETAIL:  Key (chem_ids)=({}) already exists.
CONTEXT:  SQL statement "UPDATE mixtures AS m
   SET    chem_ids = m.chem_ids - o.chem_ids  -- assuming this won't upset sort order!
   FROM  (
      SELECT mixture_id, array_agg(chem_id) AS chem_ids
      FROM   old_table
      GROUP  BY 1
      ) o
   WHERE m.mixture_id = o.mixture_id
   AND   m.chem_ids IS DISTINCT FROM (m.chem_ids - o.chem_ids)"
PL/pgSQL function trg_mixture_chems_delaft() line 3 at SQL statement