By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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
);
CREATE TABLE
CREATE TABLE
CREATE TABLE
WITH input_sequence AS (
SELECT
generate_series AS index_id
FROM
generate_series(1, 5)
),
new_accounts AS (
INSERT INTO accounts
SELECT
nextval('accounts_id_seq')
FROM
input_sequence
RETURNING
*
),
input_accounts AS (
SELECT
input_sequence.index_id,
new_accounts.id
FROM
input_sequence CROSS JOIN new_accounts
),
new_profiles AS (
INSERT INTO profiles
SELECT
nextval('profiles_id_seq')
FROM
input_sequence
RETURNING
*
),
input_profiles AS (
SELECT
input_sequence.index_id,
new_profiles.id
FROM
ERROR: cannot insert into column "id" DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override.