0

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
  • Please insert: DDL of each table (maybe simplified by removing unnesessary fields), sample data for each table, the result you need for that data. – Akina Apr 10 at 12:51
  • @Akina is this enough or something else needed? – Marko Taht Apr 10 at 13:05
0
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.