I'm assuming your question is about the query that generates the error message. Please edit your question and make clear what you are asking about if this is not the case, and provide a Minimal, Complete, and Verifiable Example.
To address the query error, define the CTEs in the top level and not in the sub-select:
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;
Reference: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING
That said, for your schema to work as described, tag_attributes.id should be defined as bigint and not bigserial. If, for some reason, you need to add rows to tag_attributes that don't have a matching row in tag, change tag.id to bigint, create a separate sequence with CREATE SEQUENCE, and use that to generate IDs for both tables. Otherwise the sequences will collide and chaos will ensue.
EDIT 1
So you want to upsert on tag_attributes if id already exists? Just add ON CONFLICT DO UPDATE to your final INSERT:
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;
EXCLUDED is a magic table name that has the new data, and (id) specifies which constraint triggers the DO action (in this case the primary key).
Reference: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT
