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?.
select version();
| version |
|---|
| PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit |
CREATE SEQUENCE user_id_seq;
CREATE SEQUENCE address_id_seq;
CREATE TABLE user_
(
id integer NOT NULL DEFAULT nextval('user_id_seq'::regclass),
email character varying COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT "PK_cace4a159ff9f2512dd42373760" PRIMARY KEY (id),
CONSTRAINT "UQ_e12875dfb3b1d92d7d7c5377e22" UNIQUE (email)
);
CREATE TABLE address
(
id integer NOT NULL DEFAULT nextval('address_id_seq'::regclass),
address character varying COLLATE pg_catalog."default" NOT NULL,
user_id integer,
CONSTRAINT "PK_bec464dd8d54c39c54fd32e2334" PRIMARY KEY (id),
CONSTRAINT "FK_35472b1fe48b6330cd349709564" FOREIGN KEY (user_id)
REFERENCES user_ (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
);
WITH insert_user AS
(
INSERT INTO user_ (email) VALUES ('user1@example.com')
RETURNING id
)
SELECT * FROM insert_user;
| id |
|---|
| 1 |
SELECT * FROM user_;
| id | |
|---|---|
| 1 | user1@example.com |
TRUNCATE user_ CASCADE;
BEGIN TRANSACTION;
WITH insert_user AS
(
INSERT INTO user_ (email) VALUES ('user2example.com')
RETURNING id
)
INSERT INTO address (address, user_id)
SELECT '1, Long Street, Somewhere', (SELECT id FROM insert_user)
COMMIT;
1 rows affected
SELECT * FROM user_;
| id | |
|---|---|
| 2 | user2example.com |
SELECT * FROM address;
| id | address | user_id |
|---|---|---|
| 1 | 1, Long Street, Somewhere | 2 |