Recursive Insert into clause in Postgresql

Question

I want to perform a big insert statement on multiple lines, but recursion is making it hard to build the correct SQL statement. I believe an example will make it easier to explain. Consider the model:

car

|id|code|Model name     |
|1 |100 |Deluxe         |
|10|100 |Deluxe improved|
|2 |200 |Standard       |
|20|200 |Standard new   |

color

|id|Name|
|2 |Red |
|3 |Blue|

car_colors

|id|car_id|color_id|
|3 |1     |2       |
|4 |2     |2       |
|5 |2     |3       |

The deluxe car was added, and afterwards the "deluxe improved" model was inserted. It's a new version of the same car (same code). Unfortunately, John Doe forgot to update the car_colors table, so now you want to update that table by inserting the same colors for every same car code.

In the example considered, we'd like to add the tuple "Deluxe improved, red" (because Deluxe and deluxe improved have the same code and Deluxe is available in red) and the tuples "standard new, red" and "standard new, black" for the same reasons.

The PSEUDO-CODE (non-sql) should be something like: all_cars_and_colors = select * from car left outer join car_colors

for each(this_car:all_cars_and_colors){

    if(all_cars_and_colors.color_id does not exist){
        car_colors_to_copy = select * from car inner join car_colors where car.code=this_car.code

        for each(color_to_copy: car_colors_to_copy){
            insert into car_colors(id,car_id,color_id) VALUES (nextval('id_sequence') ,this_car.id,color_to_copy.color_id)
        }

    }
}

How would one solve this using SQL?

NOTE: removed the recursive-query tag, since the problem does not require a recursive solution. — wildplasser
– wildplasser, Commented Jul 9, 2012 at 14:17
I improved the example to make it clearer. Based on the suggested answer @wildplasser , I believe it wasn't clear that the table has several items that need updating (not just the deluxe model) — mmalmeida
– mmalmeida, Commented Jul 9, 2012 at 14:23
So, business wise, should it be possible to have a car that doesn't have a color in the car_colors table? If not you should add foreign keys to make it impossible to insert a new car until it has a matching entry in car_colors to go with it. — Scott Marlowe
– Scott Marlowe, Commented Jul 10, 2012 at 13:14
@ScottMarlowe: this is just a proof of concept for the underlying issue. You are right that the business model in this example might be tweaked! :) — mmalmeida
– mmalmeida, Commented Jul 10, 2012 at 17:02

wildplasser · Accepted Answer · 2012-07-09 16:29:42Z

1

-- spoiler
INSERT INTO car_colors (car_id, color_id)
SELECT c1.id
        , co.color_id
FROM car c1
JOIN car c0 ON 1=1
JOIN car_colors co ON co.car_id = c0.id
WHERE c1. zname = 'Deluxe improved'
AND c0. zname = 'Deluxe'
        ;

UPDATE: since the requrements appear to have changed, here's a new one. CTE to the resque ...

DROP TABLE car ;
CREATE TABLE car
        ( id INTEGER NOT NULL PRIMARY KEY
        , zcode integer NOT NULL
        , zname varchar
        );
INSERT INTO car(id, zcode,zname) VALUES
(1 ,100 , 'Deluxe' )
,(10,100 ,'Deluxe improved' )
,(2 ,200 , 'Standard' )
,(20,200 , 'Standard new' )
        ;

DROP TABLE color ;
CREATE TABLE color
        ( id integer NOT NULL PRIMARY KEY
        , zname varchar
        );

INSERT INTO color(id,zname) VALUES
(2 ,'Red' ) , (3 ,'Blue' )
        ;

DROP TABLE car_colors;
CREATE TABLE car_colors
        ( id SERIAL NOT NULL PRIMARY KEY
        , car_id  integer NOT NULL REFERENCES car (id)
        , color_id  integer NOT NULL REFERENCES color (id)
        , UNIQUE (car_id,color_id)
        )
        ;
INSERT INTO car_colors (car_id, color_id) VALUES
  (1,2) , (2,2) , (2,3)
        ;

WITH carmap AS (
        SELECT c0.id AS orgcar
                , c1.id AS newcar
        FROM car c1
        -- This is an ugly join based on a substring
        JOIN car c0 ON c1.zname ~ c0.zname AND c1.id <> c0.id
        )
INSERT INTO car_colors (car_id, color_id)
SELECT cm.newcar
        , co.color_id
FROM carmap cm
JOIN car_colors co ON co.car_id = cm.orgcar
WHERE NOT EXISTS ( SELECT *
        FROM car_colors nx
        WHERE nx.car_id = cm.newcar
        AND nx.color_id = co.color_id
        )
        ;

edited Jul 9, 2012 at 16:29

answered Jul 9, 2012 at 14:14

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

mmalmeida Over a year ago

Please check the improved example: recursion comes, I believe, when you have several models updated - so having explicit where clauses isn't an option here.

wildplasser Over a year ago

You still have an implicit connection (old_model <--> new_model), which can only be expressed in explicit literals (unless the old model name is a substring of the new model name AND the new name does not exist YET)

mmalmeida Over a year ago

your answer does not work for the given example: it will not update any other model aside from Deluxe (eg: the Standard model in the example).

mmalmeida Over a year ago

I made a change suggestion from WITH...INSERT to INSERT...WITH (as the former is only available in PG> 9.1). Also, since same models have the same code (eg: deluxe is 100), it's be easier to use the code equality instead. Otherwise, that's it!

Gordon Linoff · Accepted Answer · 2012-07-09 14:12:11Z

0

I think the query you want looks like:

insert into car_colors(car_id, color_id)
    select <deluxe improved car id>, color_id
    from car_colors
    where car_id = <deluxe car id>

This doesn't handle the id, because that should be done at the table level. You should declare the id column as a SERIAL column.

If you are concerned that the new rows are duplicates, use:

insert into car_colors(car_id, color_id)
    select <deluxe improved car id>, color_id
    from car_colors cc           
    where car_id = <deluxe car id> and
          color_id not in (select color_id from car_colors where car_id = <deluxe improved car id>)

answered Jul 9, 2012 at 14:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

Recursive Insert into clause in Postgresql

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related