I have this logic that I want to construct a query either with plain SQL or with PL/pgSQL.

So the logic is I have a large set of data, around 100,000 records that I need to upsert into two different tables

I have table_a: id, col_1, col_2, col_3

and table_b: id, table_a_id, col_4, col_5, col_6

for each record, I want to:

insert in table_a (col_1, col_2, col_3) 
values (...) on conflict (col_1, col2) 
do update ...

And then use the returning ID of table_a in table_b as following:

insert into table_b (table_a_id, col_4, col_5, col_6) 
values (...) 
on conflict (table_a_id, col_4) 
update ...

There are two solutions that I can think of off the top of my head:

  1. use a stored procedure for each insert
  2. use an AFTER trigger on the target table

For the sake of simplicity, the trigger would likely be the simplest solution.

First, define the function that will be used by the trigger:

        INSERT INTO table_b (table_a_id, col_4, col_5, col_6)
        SELECT NEW.id, NEW.col_4, NEW.col_5, NEW.col_6
            ON CONFLICT ON CONSTRAINT idx_pri_key
            DO UPDATE SET col_4 = NEW.col_4;
        RETURN NEW;
    END ; $$ LANGUAGE 'plpgsql';

With the function written, we can now set it to trigger on every INSERT and UPDATE on the original table:

       EXECUTE PROCEDURE setTableBRecord();

Now every time you do an INSERT or UPDATE on table_a, a record will be recorded in table_b.

Important Note: this will have a performance impact on writes to table_a. If the requirement to have the data written in both locations does not need to be permanent, consider dropping the trigger after the data import.

If nothing else, I hope this gives you something to think about.

  • 1
    If I'm inserting into table_a only , where can I set the values for table b? I only need one column value from table_a, the rest are new values – fareed Apr 26 at 7:41
  • You set the values for table_b in the function that is called by the trigger. These values will be found in a pseudo table called NEW. So you may have something like NEW.id, NEW.col_a, NEW.col_b, etc.. The answers you seek are all in the two code blocks above. – matigo Apr 26 at 7:45
  • The trigger here is just pulling value from table_a and populates table_b. I only need one value from table a (which is the ID) the rest of the values are inserted (col_4 in table_b is not referenced in table_a) – fareed Apr 26 at 15:03
  • You do not need to use the exact trigger that I wrote. I do not know your exact use case nor is it any of my business. If all you need is the id, you need only the NEW.id value to be specified in the trigger. If you are asking me to provide an exact solution for your situation, I will require some Quatloos and a specification that is less ambiguous :P – matigo Apr 26 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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