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 tag (
  id        uuid DEFAULT gen_random_uuid() PRIMARY KEY
, column1  text
, column2 text
);
ALTER TABLE tag ADD CONSTRAINT tag_un UNIQUE (column1,column2);


CREATE TABLE tag_attributes (
  id        uuid PRIMARY KEY
, hot1 text
, hot2 text
, hot3 text
);
with s as (
    select id, column1, column2
    from tag
    where column1 = 'key1' and column2 = 'value1'
), i as (
    insert into tag (column1, column2)
    select 'key1', 'value1'
    where not exists (select 1 from s)
    returning id
)
insert into tag_attributes (id, hot1, hot2, hot3) 
select id, 'var1', 'var1', 'var1'
from i
union all
select id, 'var1', 'var1', 'var1'
from s;
1 rows affected

with s as (
    select id, column1, column2
    from tag
    where column1 = 'key1' and column2 = 'value1'
), i as (
    insert into tag (column1, column2)
    select 'key1', 'value1'
    where not exists (select 1 from s)
    returning id
)
insert into tag_attributes (id, hot1, hot2, hot3) 
select id, 'var2', 'var2', 'var2'
from i
union all
select id, 'var2', 'var2', 'var2'
from s;
ERROR:  duplicate key value violates unique constraint "tag_attributes_pkey"
DETAIL:  Key (id)=(99c5bbfc-c1a0-4ca5-9cf2-b884ada6ab1f) already exists.


with s as (
    select id, column1, column2
    from tag
    where column1 = 'key1' and column2 = 'value1'
), i as (
    insert into tag (column1, column2)
    select 'key1', 'value1'
    where not exists (select 1 from s)
    returning id
)
insert into tag_attributes (id, hot1, hot2, hot3) 
select id, 'var1', 'var1', 'var1'
from i
union all
select id, 'var1', 'var1', 'var1'
from s
on conflict (id) do update
set hot1=EXCLUDED.hot1, hot2=EXCLUDED.hot2, hot3=EXCLUDED.hot3;
1 rows affected

select t.*, ta.hot1, ta.hot2, ta.hot3 from tag t, tag_attributes ta WHERE t.id=ta.id;
-- result I want after the execution --
select t.*, 'var2' as hot1, 'var2' as hot2, 'var2' as hot3 from tag t, tag_attributes ta where t.id=ta.id; 


id
column1
column2
hot1
hot2
hot3


99c5bbfc-c1a0-4ca5-9cf2-b884ada6ab1f
key1
value1
var1
var1
var1




id
column1
column2
hot1
hot2
hot3


99c5bbfc-c1a0-4ca5-9cf2-b884ada6ab1f
key1
value1
var2
var2
var2