PostgreSQL use uuid_generate_v4() used in INSERT...SELECT in a later UPDATE statement

Question

I'm writing a database migration that adds a new table whose id column is populated using uuid_generate_v4(). However, that generated id needs to be used in an UPDATE on another table to associate the entities. Here's an example:

BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE IF NOT EXISTS models(
   id,
   type
);

INSERT INTO models(id)
SELECT 
  uuid_generate_v4() AS id
 ,t.type
FROM body_types AS t WHERE t.type != "foo";

ALTER TABLE body_types
   ADD COLUMN IF NOT EXISTS model_id uuid NOT NULL DEFAULT uuid_generate_v4();

UPDATE TABLE body_types SET model_id = 
  (SELECT ....??? I'M STUCK RIGHT HERE) 

This is obviously a contrived query with flaws, but I'm trying to illustrate that what it looks like I need is a way to store the uuid_generate_v4() value from each inserted row into a variable or hash that I can reference in the later UPDATE statement.

Maybe I've modeled the solution wrong & there's a better way? Maybe there's a postgresql feature I just don't know about? Any pointers greatly appreciated.

Answer 1

I was modeling the solution incorrectly. The short answer is "don't make the id in the INSERT random". In this case the key is to add the 'model_id' column to 'body_types' first. Then I can use it in the INSERT...SELECT without having to save it for later use because I'll be selecting it from the body_types table.

BEGIN;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

ALTER TABLE body_types
   ADD COLUMN IF NOT EXISTS model_id uuid NOT NULL DEFAULT uuid_generate_v4();

CREATE TABLE IF NOT EXISTS models(
   id,
   type
);

INSERT INTO models(id)
SELECT 
  t.model_id AS id
 ,t.type
FROM body_types AS t WHERE t.type != "foo";
 

Wish I had a better contrived example, but the point is, avoid using random values that you have to use later, and in this case it was totally unnecessary to do so anyway.