Use WITH queries to do UPSERT

upsert_using_with_queries.sql

WITH creations_need_move AS (
  SELECT id, type
    FROM creations
   WHERE type IS NOT NULL
),
update_existing_metadata AS (
  UPDATE creation_metadata (creation_id, category)
     SET category = source.type
    FROM creations_need_move source
   WHERE creation_id = source.id
   RETURNING creation_metadata.*
)
INSERT INTO creation_metadata (creation_id, category)
     SELECT id, type
       FROM creations_need_move
      WHERE NOT EXISTING ( SELECT 1
                             FROM update_existing_metadata up
                            WHERE up.creation_id = creations_need_move.id )