CODEWITHSUNDEEP
phpdatabasepostgresqlinsertsql-insert
Ifta
Ifta

Reputation: 1595

Insert data in 3rd table with the values inserted in 2 other table

I have 3 table in postgres database. Created with this code:

CREATE TABLE AUTHOR(
    ID SERIAL PRIMARY KEY,
    NAME TEXT
);
CREATE TABLE BOOK(
    ID SERIAL PRIMARY KEY,
    NAME TEXT
);
CREATE TABLE BOOK_AUTHOR(
    BOOK_ID INTEGER REFERENCES  BOOK(ID),
    AUTHOR_ID INTEGER REFERENCES AUTHOR(ID)
);

A book can have multiple author.

I want to insert multiple author in AUTHOR table.

A book in BOOK table.

And pair in BOOK_AUTHOR table.

For example: If BOOK X is written by Mr. A and Mr. B

I want the table content be like this

AUTHOR
ID-NAME
1, Mr. A
2, Mr. B

BOOK
ID-NAME
1, X

BOOK_AUTHOR
BOOK_ID-AUTHOR_ID
1,1
1,2

I am using postgres-php.

I know I can insert data in author table. Insert data in book table. Make query over them to get the ids.

Then insert in book_author table.

But is there any way to insert those data more efficiently?

What is the possible best way?

Upvotes: 2

Views: 278

Answers (1)

MyBrainHurts
MyBrainHurts

Reputation: 2630

PostgreSQL has a very handy 'RETURNING' function you can use here like this:

WITH authors AS (
    INSERT INTO 
        author (name) 
    VALUES 
        ('Mr. A'), ('Mr. B')
    RETURNING
        id
), books AS (
    INSERT INTO
        book (name)
    VALUES
        ('X')
    RETURNING
        id
)
INSERT INTO
    book_author
SELECT
    b.id
    , a.id
FROM
    books b
    , authors a;

Just make a Cartesian product of the output and use it as input for the third insert.

Upvotes: 1

Related Questions