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 tbl(
email varchar(20),
shipping varchar(20),
country varchar(20),
country_sel varchar(20)
);
insert into tbl (email, shipping, country, country_sel)
values
('1@ex.com', 'US', 'US', 'USA'),
('2@ex.com', 'US', null, 'US'),
('3@ex.com', 'CA', 'CA', 'US'),
('4@ex.com', 'AU', 'AU', null)
;
select * from tbl;
4 rows affected
| shipping | country | country_sel | |
|---|---|---|---|
| 1@ex.com | US | US | USA |
| 2@ex.com | US | null | US |
| 3@ex.com | CA | CA | US |
| 4@ex.com | AU | AU | null |
select email, shipping, country, country_sel
from tbl
cross join lateral (
select count(*) n
from (
select *
from (values (shipping),(country),(country_sel)) t1(c)
where c is not null
except
select *
from (values ('US'),('U.S'),('USA')) t2(c)
) t
) t
where t.n = 0;
| shipping | country | country_sel | |
|---|---|---|---|
| 1@ex.com | US | US | USA |
| 2@ex.com | US | null | US |