PostgreSQL: Insert records into other tables that have a reference from the main table

I want to achieve such that after executing the query:

INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')

the accound_id and initial value are automatically inserted into all other tables that have a common structure.

Here are all the tables I have:

CREATE TABLE IF NOT EXISTS accounts(
    unique_id uuid,
    account_id BIGINT NOT NULL UNIQUE GENERATED ALWAYS AS IDENTITY,
    PRIMARY KEY (unique_id, account_id)
);

CREATE TABLE IF NOT EXISTS currency_golden_credit (
    ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
    balance INT DEFAULT 0 NOT NULL,
    CONSTRAINT fk_account FOREIGN KEY (ref_id) 
      REFERENCES accounts (account_id)
      ON DELETE CASCADE
      ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS currency_iron_credit (
    ref_id BIGINT NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
    balance INT DEFAULT 0 NOT NULL,
    CONSTRAINT fk_account FOREIGN KEY (ref_id) 
      REFERENCES accounts (account_id)
      ON DELETE CASCADE
);

I didn't find any solution and tried the query, but it also turned out to be incorrect:

with rows as (
  INSERT INTO accounts (unique_id) VALUES
    ('c78f139a-7d71-429b-b55c-0d8fad481959')
    RETURNING account_id
) 

INSERT INTO currency_golden_credit (ref_id) SELECT account_id
INSERT INTO currency_iron_credit (ref_id) SELECT account_id
FROM rows;

Solution

As @Frank Heikens mentioned, you need to review the model you are working with.Your attempt is on the right track. However, you need to modify the query to ensure that the account_id generated from the main table insertion is used in the subsequent inserts into the other tables.

WITH rows AS (
  INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')
  RETURNING account_id
) 
INSERT INTO currency_golden_credit (ref_id) SELECT account_id FROM rows
INSERT INTO currency_iron_credit (ref_id) SELECT account_id FROM rows;

In this query below, I used the RETURNING clause after the insertion into the accounts table to retrieve the generated account_id. The WITH clause assigns the returned account_id to the rows CTE (Common Table Expression).

Then, I added the subsequent INSERT statements into the other tables currency_golden_credit and currency_iron_credit using a SELECT statement from the rows CTE to obtain the account_id.