The error you get: ON CONFLICT DO UPDATE command cannot affect row a second time … indicates you are trying to upsert the same row more than once in a single command. In other words: you have dupes on (name, url, email) in your VALUES list. Fold duplicates (if that’s an option) and the error … Read more
You can do so starting with Postgres 9.1: with rows as ( INSERT INTO Table1 (name) VALUES (‘a_title’) RETURNING id ) INSERT INTO Table2 (val) SELECT id FROM rows In the meanwhile, if you’re only interested in the id, you can do so with a trigger: create function t1_ins_into_t2() returns trigger as $$ begin insert … Read more
Use data-modifying CTEs: WITH ins1 AS ( INSERT INTO sample(firstname, lastname) VALUES (‘fai55’, ‘shaggk’) — ON CONFLICT DO NOTHING — optional addition in Postgres 9.5+ RETURNING id AS sample_id ) , ins2 AS ( INSERT INTO sample1 (sample_id, adddetails) SELECT sample_id, ‘ss’ FROM ins1 RETURNING user_id ) INSERT INTO sample2 (user_id, value) SELECT user_id, ‘ss2’ … Read more
The currently accepted answer seems ok for a single conflict target, few conflicts, small tuples and no triggers. It avoids concurrency issue 1 (see below) with brute force. The simple solution has its appeal, the side effects may be less important. For all other cases, though, do not update identical rows without need. Even if … Read more