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 A (
"raw_number" INTEGER,
"ars" INTEGER,
"bill" INTEGER,
"codt" VARCHAR(7),
"c4" INTEGER
);
INSERT INTO A
("raw_number", "ars", "bill", "codt", "c4")
VALUES
('1', '900', '603121','', '123'),
('2', '900', '603121', 'YPR-003', '234'),
('3', '900', '603121', 'Psp-123', '345'),
('4', '900', '603121', '', '456'),
('5', '900', '603121', '', '567');
CREATE TABLE
INSERT 0 5
CREATE TABLE B (
"raw_number" INTEGER,
"ars" INTEGER,
"bill" INTEGER,
"codt" VARCHAR(7),
"s4" VARCHAR(3)
);
INSERT INTO B
("raw_number", "ars", "bill", "codt", "s4")
VALUES
('1', '900', '603121', 'Psp-123', 'asd'),
('2', '900', '603121', '', 'zxc'),
('3', '900', '455000', '', 'F');
CREATE TABLE
INSERT 0 3
SELECT a.ars, a.bill, a.codt, a.c4, COALESCE(b.s4,(SELECT s4 FROM B b1 WHERE a.ars = b1.ars AND a.bill = b1.bill and codt = ''))
FROM A a
LEFT JOIN B b ON
a.ars = b.ars AND a.bill = b.bill AND a.codt = b.codt
| ars | bill | codt | c4 | coalesce |
|---|---|---|---|---|
| 900 | 603121 | 123 | zxc | |
| 900 | 603121 | 456 | zxc | |
| 900 | 603121 | 567 | zxc | |
| 900 | 603121 | Psp-123 | 345 | asd |
| 900 | 603121 | YPR-003 | 234 | zxc |
SELECT 5