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 Table_1(Address1 varchar(100), Cty varchar(2));
insert into Table_1 values
('245 laguna niguel 92677','US')
,('942456 AMK 423' ,'SG')
,('Tuas 02-22 098733','SG')
,('Tuas 098733 12-22','SG')
;
create table Table_2(Cty varchar(2), Postal int);
insert into Table_2 values
('US', 5)
,('SG', 6)
;
select * from Table_1;
select * from Table_2;
CREATE TABLE
INSERT 0 4
CREATE TABLE
INSERT 0 2
| address1 | cty |
|---|---|
| 245 laguna niguel 92677 | US |
| 942456 AMK 423 | SG |
| Tuas 02-22 098733 | SG |
| Tuas 098733 12-22 | SG |
SELECT 4
| cty | postal |
|---|---|
| US | 5 |
| SG | 6 |
SELECT 2
select *
,substring(address1
,strpos(translate(address1,'0123456789','9999999999'),substring('9999999999',1,postal))
,postal) code
from Table_1 t1
left join Table_2 t2 on t2.Cty=t1.Cty
;
| address1 | cty | cty | postal | code |
|---|---|---|---|---|
| 245 laguna niguel 92677 | US | US | 5 | 92677 |
| Tuas 098733 12-22 | SG | SG | 6 | 098733 |
| Tuas 02-22 098733 | SG | SG | 6 | 098733 |
| 942456 AMK 423 | SG | SG | 6 | 942456 |
SELECT 4