Given the tables like this: https://dbfiddle.uk/Z8hOhnYG
CREATE TABLE accounts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, ); CREATE TABLE profiles ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY ); CREATE TABLE account_profiles ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, account_id bigint NOT NULL REFERENCES accounts, profile_id bigint NOT NULL REFERENCES profiles );
The requirements are:
- every account must always have at least one profile tied to it.
- therefore a new account must always create a new profile and add their relation row to the database
- nothing is created when the entire operation fails for any reason.
So for the purpose of batching I'd like to write it as a multi-insert query and so I came up with this algorithm:
- create a series of IDs with the same length as the number of accounts
- add accounts
- join new accounts with the series
- add profiles
- join new profiles with the series
- join account and profile series tables on their series id and insert the result into the relations table
- return new accounts