I need to write a migration. There is a profile_details table and an account table. I need to create an record in profile_details for each record in the account table and set the profile_details_id field from the account table to any id from the profile_details table. It doesn't matter which id the account record will get, all records at the beginning will be the same, the main thing is that they are unique.

with profile as (
            INSERT INTO profile_details 
            (id, company_name, country_code, address)
            SELECT uuid_generate_v4(), '', '', ''
            from account    
            returning *
        )
        update account
            Set profile_details_id = profile.id
            FROM profile

This option does not work due to an error

ERROR:  duplicate key value violates unique constraint "UQ_1b48abd3c37e09aac8235b3cd22"
DETAIL:  Key (profile_details_id)=(0ee5ead1-c0f0-4cd3-ae60-a1b493b0d460) already exists.
SQL state: 23505

CodePudding user response：

You just have to switch your UPDATE and INSERT statements:

• Assign a random UUID to each account and use RETURNING to get a list of the created UUIDs.
• Use those returned UUIDs to create new records in the profile_details table.

WITH new_uuids AS (
  UPDATE account
  SET profile_details_id = uuid_generate_v4()
  RETURNING profile_details_id
)

INSERT INTO profile_details(id, company_name, country_code, address)
SELECT
  profile_details_id, '', '', ''
FROM new_uuids