Postgresql – Add missing rows to table via checking relations between other tables

postgresql

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

Best Answer

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.

Related Solutions

Postgresql – Altering a parent table in Postgresql 8.4 breaks child table defaults

Your problem is that when you add a new column to the_person, its child, the_person_two will have this field appended at the end of columns list (4th position), so after has_default column. See:

db=> \d temp_person
  Column   |       Type        |                            Modifiers                            
-----------+-------------------+-----------------------------------------------------------------
 person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)
 name      | character varying | 
 foo       | text              | 

db=> \d temp_person_two 
   Column    |         Type         |                            Modifiers                            
-------------+----------------------+-----------------------------------------------------------------
 person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)
 name        | character varying    | 
 has_default | character varying(4) | not null default 'en'::character varying
 foo         | text                 |

So, when you execute this:

INSERT INTO temp_person_two VALUES ( NEW.* );

PostgreSQL will actually understand that you want to insert on the first three columns of temp_person_two (as NEW.* will expand to three values), generating something similar to this:

INSERT INTO temp_person_two(person_id,name,has_default)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

So, temp_person_two.has_default will get the value of NEW.foo, which is NULL in your case.

The solution is to simply expand the column names:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.person_id, NEW.name, NEW.foo );

or, you could also use this:

INSERT INTO temp_person_two(person_id,name,foo)
VALUES ( NEW.* );

But this is weak, as any changes on column positions may break your statements, so I'd recommend the first one.

EDIT:

So the conclusion and the lesson learned here is:

Always explicitly type the names of the columns and the values when issuing an INSERT command, in fact, when issuing any SQL command at all... =D

This will save you a lot of time solving problems like that in future.

Postgresql – PL/pgSQL function or rCTE to detect depth of relations between two tables

A recursive CTE seems the way to go.
Assuming your path has no cycles. Else it needs more work to detect cycles. The array solution below can readily be adapted.

Test setup

Building on this simplified layout:

CREATE TABLE t1 (t1_id int, objid text);
INSERT INTO t1 VALUES
 (1,'aaa')
,(2,'bbb')
,(3,'ccc')
,(4,'ddd')
,(5,'eee')
,(6,'fff')
,(7,'ggg')
,(8,'hhh');

CREATE TABLE t2 (t2_id int, t1_id int, objid text);
INSERT INTO t2 VALUES
 (1,3,'aaa')
,(2,4,'aaa')
,(3,7,'hhh')
,(4,8,'ccc');

Two different solutions:

Solution with string as path

WITH RECURSIVE cte AS (
   SELECT t.t1_id AS start_id
        , t2.t2_id::text || '(t2)' || COALESCE(' ->' || t1.t1_id || '(t1)', '') AS path
        , t1.t1_id
   FROM   t1 t
   LEFT   JOIN t2 USING (t1_id)
   LEFT   JOIN t1 ON t1.objid = t2.objid

   UNION ALL
   SELECT c.start_id
        , c.path || ' ->' || t2.t2_id || '(t2)' || COALESCE(' ->' || t1.t1_id || '(t1)', '')
        , t1.t1_id
   FROM   cte c
   JOIN   t2      USING (t1_id)
   LEFT   JOIN t1 USING (objid)
   )
SELECT DISTINCT ON (start_id)
       start_id, path
FROM   cte
ORDER  BY start_id, path DESC;

Result:

start_id   path
1          
2          
3          1(t2) ->1(t1)
4          2(t2) ->1(t1)
5          
6          
7          3(t2) ->8(t1) ->4(t2) ->3(t1) ->1(t2) ->1(t1)
8          4(t2) ->3(t1) ->1(t2) ->1(t1)

Table names are redundant, obviously. I added them for good looks.

Solution with inverted array

Rightmost element is first t2_id, keep alternating from right to left.

WITH RECURSIVE cte AS (
   SELECT t.t1_id AS start_id, ARRAY[t1.t1_id, t2.t2_id] AS path
   FROM   t1 t
   LEFT   JOIN t2 USING (t1_id)
   LEFT   JOIN t1 ON t1.objid = t2.objid

   UNION ALL
   SELECT c.start_id, t1.t1_id || (t2.t2_id || path)
   FROM   cte c
   JOIN   t2 ON t2.t1_id = path[1]
   LEFT   JOIN t1 USING (objid)
   )
SELECT DISTINCT ON (start_id)
       start_id, array_remove(path, NULL) AS path
FROM   cte
ORDER  BY start_id, array_length(path, 1) DESC;

Result:

start_id   path
1          {}
2          {}
3          {1,1}
4          {1,2}
5          {}
6          {}
7          {1,1,3,4,8,3}
8          {1,1,3,4}

array_remove() requires Postgres 9.3+.

Inverted the array to need one less columns. By putting the last element first, I can reference path[1] in the next step. Not sure if that's cheaper, would need a test ...

Shorter code, but probably slower. I suspect array handling is more expensive. Easier to adapt if we need to observe cycles.

SQL Fiddle.

Major points

We are alternating between two tables.
To make this recursive, one step needs to cover two hops (from t1 -> t2 and back t2 -> t1).
The initial SELECT uses 2x LEFT JOIN to include all rows like in your example result.
The recursive part JOIN to stop the loop when no match is found. The hop back uses LEFT JOIN again.