Comment by pjungwir

A common pattern I still haven't solved is this: I have table A, and now I want to add table B and give it one row for each row in table A. Then I want to add a foreign key column to table A pointing at the row I just added.

Of course this is easy to do with a loop, but can you do it all in pure SQL? If you have a solution I would love to see it.

Here is my history of attempts:

    DROP TABLE IF EXISTS dogs;
    DROP TABLE IF EXISTS doghouses;
    DROP SEQUENCE IF EXISTS dogs_id_seq;
    DROP SEQUENCE IF EXISTS doghouses_id_seq;


    BEGIN;

    CREATE SEQUENCE dogs_id_seq;
    CREATE SEQUENCE doghouses_id_seq;

    CREATE TABLE dogs (
      id INTEGER PRIMARY KEY DEFAULT nextval('dogs_id_seq'),
      name TEXT NOT NULL
    );

    INSERT INTO dogs
    (name)
    VALUES
    ('Sparky'),
    ('Spot')
    ;

    -- Now we want to give each dog a doghouse:
    CREATE TABLE doghouses (
      id INTEGER PRIMARY KEY DEFAULT nextval('doghouses_id_seq'),
      name TEXT NOT NULL
    );
    ALTER TABLE dogs ADD COLUMN doghouse_id INTEGER REFERENCES doghouses (id);

    /*
    -- ERROR:  syntax error at or near "INTO"
    UPDATE  dogs AS d
    SET     doghouse_id = (
      INSERT INTO doghouses
      (name) VALUES (d.name)
      RETURNING id
    )
    ;
    */

    /*
    -- ERROR:  WITH clause containing a data-modifying statement must be at the top level
    UPDATE  dogs AS d
    SET     doghouse_id = (
      WITH x AS (
        INSERT INTO doghouses
        (name) VALUES (d.name)
        RETURNING id
      ) SELECT * FROM x
    )
    ;
    */

    /*
    -- ERROR:  missing FROM-clause entry for table "dogs"
    WITH homes AS (
      INSERT INTO doghouses
      (name)
      SELECT  name
      FROM    dogs
      RETURNING doghouses.id AS doghouse_id, dogs.id AS dog_id
    )
    UPDATE  dogs
    SET     doghouse_id = homes.doghouse_id
    FROM    homes
    WHERE   dogs.id = homes.dog_id
    ;
    */

    -- ERROR:  syntax error at or near "INTO"
    UPDATE  dogs AS d1
    SET     doghouse_id = h.id
    FROM    dogs d2
    INNER JOIN LATERAL (
      INSERT INTO doghouses
      (name) VALUES (d2.name)
      RETURNING id
    )
    ON true
    WHERE d1.id = d2.id
    ;


    COMMIT;