Postgres 9.5

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 9.5.15 on x86_64-pc-linux-gnu (Debian 9.5.15-1.pgdg80+1), compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2, 64-bit


create table table_a (country_name varchar(255));
insert into table_a (country_name) values
('us'), ('usa '), ('united states of america');

create table table_b (id int, string_bone varchar(255), country_name varchar(255));
insert into table_b (id, string_bone, country_name) values
(1, 'usa', 'united states'), (2, 'u s a', 'united states'), (3, 'america', 'united states');
3 rows affected

3 rows affected

select * from table_a;
select * from table_b;


country_name


us

usa

united states of america




id
string_bone
country_name


1
usa
united states

2
u s a
united states

3
america
united states


    UPDATE table_a a
    SET country_name = b.country_name
    WHERE EXISTS (
      SELECT country_name
      FROM table_b
      WHERE a.country_name LIKE '%' || string_bone || '%') b;
ERROR:  syntax error at or near "b"
LINE 6: ...     WHERE a.country_name LIKE '%' || string_bone || '%') b;
                                                                     ^