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 test_1 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TABLE test_2 (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);
CREATE TABLE test_refs (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
id_1 bigint NOT NULL REFERENCES test_1,
id_2 bigint NOT NULL REFERENCES test_2
);
WITH new_test_1_rows AS (
INSERT INTO test_1
DEFAULT VALUES
RETURNING *
), new_test_2_rows AS (
INSERT INTO test_2
DEFAULT VALUES
RETURNING *
), test_row_pairs AS (
INSERT INTO test_refs
( id_1, id_2 )
VALUES
(
(SELECT id FROM new_test_1_rows),
(SELECT id FROM new_test_1_rows)
)
RETURNING *
)
SELECT *
FROM test_row_pairs
| id | id_1 | id_2 |
|---|---|---|
| 1 | 1 | 1 |