Use PL/PGSQL to do UPSERT

upsert_using_plpgsql.sql

CREATE OR REPLACE FUNCTION upsert(target_id INT, type_value TEXT) RETURNS VOID AS $$
  BEGIN
    -- Try update first
    UPDATE creation_metadata SET category = type_value
     WHERE creation_id = target_id;
    -- Return if UPDATE command runs successfully
    IF FOUND THEN
      RETURN;
    END IF;
    -- Since there's no record in creation_metada
    -- We then add new row with INSERT command
    INSERT INTO creation_metadata (creation_id, category)
         VALUES (target_id, type_value);
  END;co
$$ LANGUAGE 'plpgsql';
SELECT upsert(id, type) FROM creations
 WHERE type IS NOT NULL