1

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:

  1. create a series of IDs with the same length as the number of accounts
  2. add accounts
  3. join new accounts with the series
  4. add profiles
  5. join new profiles with the series
  6. join account and profile series tables on their series id and insert the result into the relations table
  7. return new accounts
0

1 Answer 1

1

GENERATED ALWAYS

You're going have to do insert-returning or use lastval()

After some discussion we arrived on this:

WITH input_sequence AS (
  SELECT
    generate_series AS index_id
  FROM
    generate_series(1, 5)
),
new_accounts AS (
  INSERT INTO accounts
  SELECT
  FROM
    input_sequence
  RETURNING    *
),
input_accounts AS (
  SELECT
    row_number() over () as index,
    new_accounts.id
  FROM
    new_accounts
),
new_profiles AS (
  INSERT INTO profiles 
    SELECT 
    FROM 
      input_sequence
    RETURNING
    *
),
input_profiles AS (
  SELECT
    row_number() over () as index,
    new_profiles.id
  FROM
    new_profiles
),
new_account_profile_pairs AS ( 
  INSERT INTO account_profiles ( account_id, profile_id )
  SELECT
    input_accounts.id AS account_id,
    input_profiles.id AS profile_id
  FROM
    input_accounts,
    input_profiles
  WHERE 
    input_accounts.index = input_profiles.index
)
SELECT
  index, 
  id
FROM
  input_accounts
ORDER BY 
  id;

Here I'm using the window function row_bnumber() over () to assign row numbers to each new inserted row giving a number that they can be joined on in the final result set.

13
  • Wait I ran the counts and it's not supposed to have 125 account_profiles, or 25 input_accounts/input_profiles: dbfiddle.uk/u4IMGftY . How do you do 1-to-1 join on the generated series when their row counts are equal? I assumed CROSS JOIN would do just that, but it seems to create all index variations of rows instead. Sep 20 at 10:50
  • yeah you don't want a cross join, perjhaps a regular join on a.id %5 = b.id%5
    – Jasen
    Sep 20 at 11:43
  • I've made a new query it's shorter, but it uses the number of results in 4 different places,
    – Jasen
    Sep 20 at 11:56
  • Well in this case it would work, but in the real code series IDs and primary keys will never match up, so there is no condition for inner join outside of "record index" if it's a thing. Sep 20 at 12:06
  • you simplfied the example so far that it is hard to guess what features are actually needed , I have another query that is closer in structure to your original
    – Jasen
    Sep 20 at 12:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.