2

I have these two table that I would like to insert alot of data into.

CREATE TABLE IF NOT EXISTS N ( id INT GENERATED ALWAYS AS IDENTITY, name varchar(50) ); CREATE TABLE IF NOT EXISTS ConnectionProps ( connectionProp INT, FK_n INTEGER, PRIMARY KEY (connectionProp,FK_n), CONSTRAINT FK_n FOREIGN KEY(id) REFERENCES N(id) ON DELETE CASCADE );

I need to insert into both tables at the same time as the primary key of the connectionprops table is a compound key of the foreignkey of N and connectionProps. How do I do this?

Improve this question
2
  • 2
    FOREIGN KEY(id), should that be FOREIGN KEY(FK_n)? Commented Jul 11, 2021 at 19:38
  • 1
    How are you inserting "a lot of data"? If you are going to loop insert statements again and again then you are going wrong already. You should use some sort of bulk insert mechanism Commented Jul 11, 2021 at 22:22

2 Answers 2

Reset to default
4

Use a CTE and INSERT ... RETURNING:

WITH x AS ( INSERT INTO n (name) VALUES ('Willi') RETURNING id ) INSERT INTO connectionprops (connectionprop, fk_n) SELECT 42, id FROM x;
Improve this answer
3
  • this approach is flawed, as nobody add two related tables in a mass insert, ass all rows are diffrent Commented Jul 13, 2021 at 12:09
  • @nbk What makes you so sure of that? Commented Jul 13, 2021 at 13:00
  • 1
    It is not flawed at all. The approach can easily be generalized for inserting multiple rows (in both tables) and taking care of the different number of inserts (eg when you insert two connectionProps with the same name). See Erwin's and my answer in this question: How do I insert a row which contains a foreign key? Commented Jul 13, 2021 at 13:21
1

You can do following

Where currval('N_id_seq')holds the last inserted id.

you had some small errors in your table definitions which i corrected to get a sample

CREATE TABLE IF NOT EXISTS N ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name varchar(50) ); 
CREATE TABLE IF NOT EXISTS ConnectionProps ( connectionProp INT, FK_n INTEGER, PRIMARY KEY (connectionProp,FK_n), CONSTRAINT FK_n1 FOREIGN KEY(FK_n) REFERENCES N(id) ON DELETE CASCADE ); 
 ✓ 
BEGIN; INSERT INTO N ("name") VALUES('test'); INSERT INTO ConnectionProps (connectionProp,FK_n) VALUES (1,currval('N_id_seq')); COMMIT; 
 ✓

1 rows affected

1 rows affected

✓ 

SELECt * FROM ConnectionProps 
 connectionprop | fk_n -------------: | ---: 1 | 1

db<>fiddle here

Improve this answer
2
  • 1
    You are making unwarranted assumptions about the name of the sequence. Commented Jul 13, 2021 at 4:48
  • currval() only returns a single value so this approach is useless when inserting more than one row. Commented Jul 13, 2021 at 6:59

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.