How to populate a postgres table with the primary key of two other tables?

Viewed 23

I have a 3 postgres tables as mentioned below,

CREATE TABLE document_type 
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    status SMALLINT NOT NULL
);

CREATE TABLE opco
(
    id SERIAL PRIMARY KEY,
    opco_name VARCHAR(50) NOT NULL,
    status SMALLINT NOT NULL
);

CREATE TABLE opco_document_type 
(
    opco_id INT NOT NULL,
    document_type_id INT NOT NULL,
    FOREIGN KEY (opco_id) 
         REFERENCES opco(id) 
             ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (document_type_id) 
         REFERENCES document_type(id) 
             ON DELETE CASCADE ON UPDATE CASCADE
);

I have already populate the opco and document_type tables with sample data. Now I need to populate the opco_document_type table in a way where each opco row should be mapped with entire document_type rows. I can populate the table manually, But it's taking time. Ex, if both opco and document_type tables have 100 rows each, then opco_document_type should have 100 x 100 rows.

I tried using below query, but I couldn't complete it.

INSERT INTO opco_document_type(opco_id, document_type_id) 
    SELECT op.id, doc.id
    FROM opco op 
0 Answers
Related