Postgresql: 'upserting' into two tables (vertical partitioning) using the same id with a unique constraint

Question

I have two tables, one containing all the hot columns and one the static ones. The static table has an unique constraint. When the conflict on the unique constraint triggers only the hot columns in the other table should be updated using the the id from the static table, if it doesn't then we want to create one row in tag and one row in tag_attributes both with the same id to join them. Since it's done using a python script I could do something like "select id from tag where ..." and react whether I get an ID or not, but that doesn't feel good.

For better clarity some code: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=027a104bd2cbbec6d70de1a5766e9c76

What I'm trying to achieve is upserting (so insert in both tables if unique does not exist and if the unique does exist insert (or update) the _hot table.

Any help would be greatly apreciated :)

Answer 1

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