0

How can I make an insert in postgresql into this two tables if the id_table1(pk) in table1 has data type serial(autoincrement) and id_table2 in table2 data type is also serial(autoincrement)? 

table1
id_table1(PK) columnA columnB
1             a       a
2             a       b

table2
id_table2(PK) columnC columnD  id_table1(FK)
1             a        b        1
2             a        b        1
3             a        b        2
4             a        a        2
juanpalomo
  • 63
  • 1
  • 8
  • do you have any issues with inserting in table 1 first before inserting into table 2 by taking the value of corresponding PK generated in table 1? – Sendhilkumar Alalasundaram Dec 17 '18 at 12:08
  • I am not quiet sure what you want to do? Please add an example of which data you want to insert and your expected output. – S-Man Dec 17 '18 at 12:17

1 Answers1

1

demo:db<>fiddle

WITH insert1 AS (
    INSERT INTO table1 (columnA, columnB) VALUES
    ('x', 'y')
    RETURNING id_table1
)
INSERT INTO table2 (columnC, columnD, id_table1) VALUES
('z', 'a', (SELECT id_table1 FROM insert1));

Using the WITH clause allows you to do one INSERT with RETURNING the new id_table1. This can be used after its execution in a second statement.

S-Man
  • 22,521
  • 7
  • 40
  • 63