0

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 :)

1 Answer 1

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

3
  • It helped a bit I think, thank you -- hopefully the dbfiddle-link helps to clarify my problem
    – BarNiy
    Commented Sep 13, 2021 at 21:53
  • Added ON CONFLICT DO UPDATE to query with documentation link.
    – dwhitemv
    Commented Sep 14, 2021 at 0:48
  • Thank you -- now I have to do a lot of reading :-)
    – BarNiy
    Commented Sep 14, 2021 at 7:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.