Postgres 9.5

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 profiles (
    id int,
    first_name text,
    last_name text
);

INSERT INTO profiles VALUES
(1, 'Jason', 'Bourne'),
(2, 'Jhonny', 'Quest');

CREATE TABLE invoices (
    invoice_id int,
    billing_address jsonb,
    profile_id int
);

INSERT INTO invoices VALUES
(1, '{"address_line1":"445 Mount Eden Road","city":"Mount Eden","country":"Auckland"}' , 2)

INSERT 0 1


SELECT
    i.invoice_id,
    jsonb_set(
        billing_address, 
        '{name}'::text[], 
        to_jsonb(concat_ws(' ', first_name, last_name))
    )
FROM 
    invoices i
JOIN profiles p ON i.profile_id = p.id


invoice_id
jsonb_set


1
{"city": "Mount Eden", "name": "Jhonny Quest", "country": "Auckland", "address_line1": "445 Mount Eden Road"}



SELECT 1


UPDATE invoices i
SET billing_address = s.new_billing_address
FROM (
    SELECT
        i.invoice_id,
        jsonb_set(
            billing_address, 
            '{name}'::text[], 
            to_jsonb(concat_ws(' ', first_name, last_name))
        ) AS new_billing_address
    FROM 
        invoices i
    JOIN profiles p ON i.profile_id = p.id
) s
WHERE s.invoice_id = i.invoice_id;

SELECT * FROM invoices


invoice_id
billing_address
profile_id


1
{"city": "Mount Eden", "name": "Jhonny Quest", "country": "Auckland", "address_line1": "445 Mount Eden Road"}
2



SELECT 1