Postgres 16

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 TYPE "TransactionStatus" AS ENUM ('COMPLETE', 'PENDING', 'REJECTED');

CREATE TYPE


CREATE TYPE "TransactionType" AS ENUM ('EXCHANGE', 'TRANSFER', 'DEPOSIT', 'WITHDRAWAL', 'CARD_IN', 'CARD_OUT', 'INVOICE_PAYMENT');

CREATE TYPE


CREATE TABLE "Account"
(
    id          SERIAL PRIMARY KEY,
    currency    "Currency"                                 NOT NULL,
)

ERROR:  syntax error at or near ")"
LINE 5: )
        ^


INSERT INTO "Account" (id, currency) VALUES (1, 'Default USD');
INSERT INTO "Account" (id, currency) VALUES (2, 'Default GBP');
INSERT INTO "Account" (id, currency) VALUES (3, 'Default EUR');
INSERT INTO "Account" (id, currency) VALUES (4, 'Default USD');
INSERT INTO "Account" (id, currency) VALUES (5, 'Default GBP');
INSERT INTO "Account" (id, currency) VALUES (6, 'Default EUR');
INSERT INTO "Account" (id, currency) VALUES (7, 'Default USD');
INSERT INTO "Account" (id, currency) VALUES (8, 'Default GBP');
INSERT INTO "Account" (id, currency) VALUES (9, 'Default EUR');

ERROR:  relation "Account" does not exist
LINE 1: INSERT INTO "Account" (id, currency) VALUES (1, 'Default USD...
                    ^


CREATE TABLE "Transaction"
(
    id                  SERIAL
        PRIMARY KEY,
    type                "TransactionType"                      NOT NULL,
    "fromAccountId"     INTEGER
                                                               REFERENCES "Account"
                                                                   ON UPDATE CASCADE ON DELETE SET NULL,
    "fromAmount"        DOUBLE PRECISION,
    "toAccountId"       INTEGER
                                                               REFERENCES "Account"
                                                                   ON UPDATE CASCADE ON DELETE SET NULL,
    "toAmount"          DOUBLE PRECISION,
    "createdAt"         TIMESTAMP(3) DEFAULT current_timestamp NOT NULL,
    status              "TransactionStatus"                    NOT NULL
)

ERROR:  relation "Account" does not exist


INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (8, 'DEPOSIT', null, null, 3, 1126062, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (9, 'DEPOSIT', null, null, 1, 8962768, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (11, 'DEPOSIT', null, null, 6, 1262031, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (12, 'DEPOSIT', null, null, 8, 8316692, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (13, 'DEPOSIT', null, null, 5, 6739586, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (15, 'DEPOSIT', null, null, 1, 2954408, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (19, 'DEPOSIT', null, null, 2, 9334989, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (21, 'DEPOSIT', null, null, 2, 3585419, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (24, 'DEPOSIT', null, null, 3, 2121075, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (26, 'DEPOSIT', null, null, 2, 2910474, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (28, 'DEPOSIT', null, null, 5, 1864872, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (29, 'DEPOSIT', null, null, 4, 1226615, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (30, 'DEPOSIT', null, null, 5, 2863291, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (31, 'DEPOSIT', null, null, 4, 2404810, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (37, 'DEPOSIT', null, null, 8, 3611272, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (38, 'DEPOSIT', null, null, 6, 7159320, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (39, 'DEPOSIT', null, null, 7, 5760753, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (40, 'DEPOSIT', null, null, 3, 1550935, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (43, 'DEPOSIT', null, null, 3, 7582280, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (44, 'DEPOSIT', null, null, 8, 4431650, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (45, 'DEPOSIT', null, null, 9, 2669120, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (46, 'DEPOSIT', null, null, 3, 8376512, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (47, 'DEPOSIT', null, null, 3, 7464642, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (52, 'WITHDRAWAL', 5, 745969, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (54, 'WITHDRAWAL', 1, 817272,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (56, 'WITHDRAWAL', 3, 490693,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (58, 'WITHDRAWAL', 4, 188072,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (59, 'WITHDRAWAL',  4, 359753,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (62, 'WITHDRAWAL',  8, 871142,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (64, 'WITHDRAWAL',  8, 598029,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (69, 'WITHDRAWAL',  1, 521680,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (70, 'WITHDRAWAL',  8, 608998,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (75, 'WITHDRAWAL',  1, 152653,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (76, 'WITHDRAWAL',  5, 168617,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (82, 'WITHDRAWAL',  6, 464464,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (83, 'WITHDRAWAL',  4, 244697,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (84, 'WITHDRAWAL',  6, 302192,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (88, 'WITHDRAWAL', 7, 484241,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (90, 'WITHDRAWAL',  2, 663081,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (96, 'WITHDRAWAL',  1, 512212,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (97, 'WITHDRAWAL', 3, 446931,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (98, 'WITHDRAWAL',  1, 280695,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (99, 'WITHDRAWAL', 8, 975881,  null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (107, 'EXCHANGE', 8, 57381, 9, 57381, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (109, 'EXCHANGE', 6, 75737, 4, 75737, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (113, 'EXCHANGE', 3, 29284, 2, 29284, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (114, 'EXCHANGE', 9, 93063, 7, 93063, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (115, 'EXCHANGE', 4, 36917, 5, 36917, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (117, 'EXCHANGE', 8, 90786, 9, 90786, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (118, 'EXCHANGE', 1, 68880, 2, 68880, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (120, 'EXCHANGE', 9, 78170, 7, 78170, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (121, 'EXCHANGE', 9, 69579, 8, 69579, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (122, 'EXCHANGE', 6, 88410, 4, 88410, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (124, 'EXCHANGE', 4, 34284, 5, 34284, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (125, 'EXCHANGE', 2, 54175, 1, 54175, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (126, 'EXCHANGE', 6, 38257, 5, 38257, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (127, 'EXCHANGE', 3, 99679, 1, 99679, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (128, 'EXCHANGE', 3, 22072, 2, 22072, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (131, 'EXCHANGE', 5, 88716, 6, 88716, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (133, 'EXCHANGE', 4, 99239, 6, 99239, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (136, 'EXCHANGE', 6, 60024, 4, 60024, 'COMPLETE');

ERROR:  relation "Transaction" does not exist
LINE 1: INSERT INTO "Transaction" (id, type, "fromAccountId", "fromA...
                    ^