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.
