1

Fiddle link: https://dbfiddle.uk/EOE627Oa

Tables

CREATE TABLE accounts (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
  login text NOT NULL,
  password text NOT NULL,
  email text,
  init_index bigint,
  parent_id bigint REFERENCES accounts
);

Input data

[
  {
    "login": "11EB19631A",
    "password": "AE128AADEF97F1E54021",
    "reference_id": 1
  },
  {
    "login": "3ED4ECBBC9",
    "password": "E67EDDB6033D02140BB4",
    "email": "a@b",
    "reference_id": 2,
    "parent_reference": 1
  },
  {
    "login": "C86D7E2CF0",
    "password": "75404617C000A0EB070C",
    "reference_id": 3,
    "parent_reference": 2
  },
  {
    "login": "C51D77BF87",
    "password": "605509993A05EE393081",
    "email": null,
    "reference_id": 4,
    "parent_reference": 2
  },
  {
    "login": "2BAB5AA533",
    "password": "DFCAB818D812B1F8F761",
    "reference_id": 5,
    "parent_reference": 3
  },
  {
    "login": "4229D47E2C",
    "password": "CE4E14ED6AD77CBC71B5",
    "email": "b@c",
    "reference_id": 6,
    "parent_reference": 2
  }
]

Query

WITH account_inits AS (
  SELECT
    row_number () OVER () as init_index,
    login,
    password,
    email,
    reference_id,
    parent_reference
  FROM
    json_to_recordset(
      $json$$json$
    ) AS input_init(
      login text,
      password text,
      email text,
      reference_id int,
      parent_reference int
    )
),
-- create new accounts
new_accounts AS (
  INSERT INTO accounts
    (
      init_index,
      login,
      password,
      email
    )
  SELECT
    init_index,
    login,
    password,
    email
  FROM
    account_inits
  RETURNING
    *
),
parent_id_pairs AS (
    SELECT
      new_accounts.id,
      account_inits.reference_id
    FROM
      new_accounts
      INNER JOIN
      account_inits
      ON
        account_inits.reference_id IN (
          SELECT DISTINCT
            parent_reference AS reference_id
          FROM
            account_inits
        )
        AND
        new_accounts.init_index = account_inits.init_index
),
account_updates AS (
  SELECT
    new_accounts.id,
    parent_id_pairs.id AS parent_id
  FROM
    new_accounts
    INNER JOIN
    account_inits
    ON
      account_inits.parent_reference IS NOT NULL
      AND
      new_accounts.init_index = account_inits.init_index
    INNER JOIN
    parent_id_pairs
    ON
      account_inits.parent_reference = parent_id_pairs.reference_id
),
updated_accounts AS (
  UPDATE
    accounts
  SET
    parent_id = account_updates.parent_id
  FROM
    account_updates
  WHERE
    account_updates.id = accounts.id
  RETURNING
    *
)
SELECT
  *
FROM
  updated_accounts
;

SELECT
  id,
  init_index,
  parent_id
FROM
  accounts
;

The problem

The table in question has a foreign key to itself to express a tree-like relationship between its rows. But these relations can be known even before inserting within batch. Therefore reference_id and parent_reference are "batch identifiers" which are generated by the application to show the references between initializers.

However the database cannot express this relation in the INSERT statement for obvious reasons. So I thought about running a separate UPDATE step in a CTE for newly created rows. But it doesn't work, despite all input data being right (you can check that by SELECT'ing the CTEs used in the UPDATE CTE), so the problem is in the UPDATE block itself. The docs list two different syntaxes for performing an UPDATE from a set of records. I tried both of them and neither apply the parent_id to the created rows.

1 Answer 1

4

You ask for updates ...

within the same transaction

But, more specifically, you are trying to do it within the same command, which makes all the difference.

Solution

Long story short:

WITH account_inits AS (
   SELECT nextval(pg_get_serial_sequence('public.accounts', 'id')) AS id, *  -- !!!
   FROM   json_to_recordset($your_json_input)
   AS input_init(login text, password text, email text, reference_id int, parent_reference int)
   )
INSERT INTO public.accounts
        (id,   login,   password,   email,   parent_id) OVERRIDING SYSTEM VALUE  -- !!!
SELECT a.id, a.login, a.password, a.email, p.id                     -- !!!
FROM   account_inits a
LEFT   JOIN account_inits p ON p.reference_id = a.parent_reference  -- !!!
RETURNING *;

fiddle

That's all. Note added details in the simplified fiddle.

This assumes referential integrity within each inserted batch: any reference_id that is actually referenced by a parent_reference must uniquely exist in the batch.

Why? How?

The main problem of your original attempt is explained here

The solution would be to run separate statements (within the same transaction). But don't go there. Use the much simpler approach as suggested: Fetch new serial IDs from the sequence with nextval() beforehand. A LEFT JOIN to self does the rest.

Related:

Since your id is GENERATED ALWAYS AS IDENTITY we must use OVERRIDING SYSTEM VALUE as demonstrated. See:

4
  • How do these preemptive ID generations interact with foreign key checks? Does the order of insertions matter in this case, since there can be a situation where a row is inserted with a parent ID of the row which isn't inserted yet. Commented Oct 31, 2022 at 10:42
  • 1
    @BillerBuilder: Since all is inserted in a single command, FK constraints are no problem. See: stackoverflow.com/a/24816197/939860 and stackoverflow.com/a/68245259/939860 Commented Oct 31, 2022 at 11:02
  • 1
    Wow the next_val() approach sure reduced a lot of back-and-forth boilerplate joins in my query. Commented Oct 31, 2022 at 14:11
  • And you can save yet another underscore! nextval() :) Commented Oct 31, 2022 at 14:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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