RDBMS change table relation OneToMany to ManyToMany. SQL script

Question

I use postgresql. In db I have important element:

|---------|    |------------|
| account |    | calendar   |
|---------|    |------------|
| id      |1--*| account_id |
|---------|    | id         |
               |------------|

and this part must be ManyToMany relation:

|---------|    |------------------|     |----------|
| account |    | account_calendar |     | calendar |
|---------|    |------------------|     |----------|
| id      |1--*| account_id       |  |-1| id       |
|---------|    | calendar_id      |*-|  |----------|
               |------------------|

I've provided update script:

CREATE TABLE account_calendar_relation (
  account_id     BIGINT REFERENCES account_table,
  calendar_id    BIGINT REFERENCES calendar
);

ALTER TABLE calendar
DROP CONSTRAINT calendar_account_id_fkey,
DROP COLUMN account_id;

this script correctly changes db-schema structure, but loses important info (mapping between calendar and account for existing data).

How my migration script could be improved with saving existing mappings between calendars and accounts?

Data example

For input data:

account | id
          1
          2

calendar | id | account_id
           1  | 1
           2  | 1

expected result:

account | id
          1
          2

calendar | id
           1 
           2 
account_calendar | account_id | calendar_id
                   1          | 1
                   1          | 2

But in actual result account_calendar table is empty (because of no dynamical population).

Answer 1

Insert following SQL to your migration script after CREATE TABLE account_calendar_relation and before ALTER TABLE calendar:

INSERT INTO account_calendar_relation(account_id,calendar_id)
SELECT account_id,id FROM calendar;