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?

2
  • 2
    FOREIGN KEY(id), should that be FOREIGN KEY(FK_n)? 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 Jul 11, 2021 at 22:22

2 Answers 2

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;
3
  • this approach is flawed, as nobody add two related tables in a mass insert, ass all rows are diffrent
    – nbk
    Jul 13, 2021 at 12:09
  • @nbk What makes you so sure of that? 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? 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

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

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.