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. For better clarity some code:
CREATE TABLE tag (
id bigserial PRIMARY KEY
, key text
, value text
-- UNIQUE (key, value) -- ?
);
CREATE TABLE tag_hotcolumns (
id bigserial PRIMARY KEY
, hot text
, stuff text
);
with s as (
select id, "key", "value"
from tag
where key = 'key1' and value = 'value1'
), i as (
insert into tag ("key", "value")
select 'key1', 'value1'
where not exists (select 1 from s)
returning id
)
select id
from i
union all
select id
from s
The second block works fine, but I can't get the returned id into the insert statement for the tag_hotcolumns...
I tried:
insert into tag_attributes (with s as (
select id, "key", "value"
from tag
where key = 'key1' and value = 'value1'
), i as (
insert into tag ("key", "value")
select 'key1', 'value1'
where not exists (select 1 from s)
returning id
)
select id, 'hot1', 'stuff1'
from i
union all
select id
from s);
And that gives me "WITH clause containing a data-modifying statement must be at the top level LINE 5: ), i as ("
Any help would be greatly apreciated :)
dwhitemv from stackexchange helped me solve this. The solution you can find here: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=f72cae495e6eed579d904a5c7b48f05b