Add missing rows to table via checking relations between other tables

Question

I have a set of tables: tender, product, product_fix, products_m_products (parent child relationship), product_m_tender. product_m_tender is missing some rows. These rows can be constructed through the use of the other tables.

Basically with tender.id i can find all products that belong to the tender via product_m_tender.tender_id. All the product id's are given by product_m_tender.product_id and product_m_tender.product_fix_id. Parent child relation is defined only for product_fix.

Is it possible to construct a insert query that inserts all the product_m_tender relations if they are not already in the table.

Something like this:

insert into product_m_tender(tender_id, product_fix_id)
values(tender_id, product_fix_id if not present in product_m_tender and (parent in product_m_tender or child in product_m_tender)) 

EDIT

tender

CREATE TABLE public.tender
(
    id bigint NOT NULL DEFAULT nextval('tender_id_seq'::regclass),
    CONSTRAINT tender_pk PRIMARY KEY (id)
)


id
__
1

product_fix

 CREATE TABLE public.product_fix
    (
        id bigint NOT NULL DEFAULT nextval('tender_id_seq'::regclass),
        CONSTRAINT tender_pk PRIMARY KEY (id)
    )

id
_
1
2
3

products_m_products

CREATE TABLE public.products_m_products
(
    parent_product_id bigint NOT NULL,
    child_product_id bigint NOT NULL,
    CONSTRAINT "pmp:pk" PRIMARY KEY (parent_product_id, child_product_id)
)

parent_product_id | child_product_id
1                 | 3
2                 | 3

product_m_Tenderer

CREATE TABLE public.product_m_tender
(
    id bigint NOT NULL DEFAULT nextval('product_m_tender_id_seq'::regclass),
    tender_id bigint NOT NULL,
    product_fix_id bigint,
    CONSTRAINT product_m_tender_pk PRIMARY KEY (id)
)

id | tender_id | product_fix_id
1  |   1       |       1
2  |   1       |       3

Expected result:

product_m_tender

    id | tender_id | product_fix_id
    1  |   1       |       1
    2  |   1       |       3
    3  |   1       |       2

Answer 1

INSERT INTO product_m_tender (tender_id, product_fix_id)
SELECT tender_id, pairs.child_product_id
FROM product_m_tender
JOIN (SELECT parent_product_id, child_product_id FROM products_m_products
      UNION
      SELECT child_product_id, parent_product_id FROM products_m_products) pairs
  ON product_m_tender.product_fix_id = pairs.parent_product_id
ON CONFLICT (tender_id, product_fix_id) DO NOTHING;

fiddle (nextvals absence fixed by a constant of zero).

Pay attention that

CONSTRAINT tender_id_product_fix_id UNIQUE (tender_id, product_fix_id) 

is added to product_m_tender table.

If possible products chain can be longer than 2 records, than execute this query until zero records affected, or move pairs subquery to recursive CTE.