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))
CREATE TABLE public.tender ( id bigint NOT NULL DEFAULT nextval('tender_id_seq'::regclass), CONSTRAINT tender_pk PRIMARY KEY (id) ) id __ 1
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
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
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
id | tender_id | product_fix_id 1 | 1 | 1 2 | 1 | 3 3 | 1 | 2