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 the_table (uid serial, name text, mail text, job_title text, has_image boolean, team_id int);
insert into the_table (uid, name) values (200, 'Lisa Beth');
CREATE TABLE
INSERT 0 1
with input (col) as (
values ('
{
"insert":{
"jsmith":{"name":"John Smith", "mail":"JSmith@smith.com","jobTitle":"Lead","hasImage":true,"teamId":35},
"lmark":{"name":"Laurendy Mark","mail":"LMark@mark.com","jobTitle":"System Admin","hasImage":true,"teamId":40}
},
"delete":{
"lbeth":{"name":"Lisa Beth","mail":"LBeth@smith.com","jobTitle":"Assistant Director","hasImage":true,"teamId":40,"uid":200}
},
"update":{}
}'::jsonb)
), do_insert as (
insert into the_table (name, mail, job_title, has_image, team_id)
select i.value ->> 'name',
i.value ->> 'mail',
i.value ->> 'jobTitle',
(i.value ->> 'hasImage')::boolean,
(i.value ->> 'teamId')::int
from input
cross join jsonb_each(col -> 'insert') i
returning *
), do_delete as (
delete from the_table
where uid in (select (i.value ->> 'uid')::int
from input
cross join jsonb_each(col -> 'delete') i)
)
update the_table
set has_image = (i.value ->> 'hasImage')::boolean
from input
cross join jsonb_each(col -> 'update') i
where (i.value ->> 'uid')::int = the_table.uid;
UPDATE 0
select *
from the_table;
| uid | name | job_title | has_image | team_id | |
|---|---|---|---|---|---|
| 1 | Laurendy Mark | LMark@mark.com | System Admin | t | 40 |
| 2 | John Smith | JSmith@smith.com | Lead | t | 35 |
SELECT 2