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