Postgres 10

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 my_table(id serial primary key, jsonb_col jsonb);
insert into my_table (jsonb_col) values
('["one", "two", "three"]'),
('["red", "green", "blue"]');

CREATE TABLE

INSERT 0 2

create or replace function jsonb_text_array(jsonb)
returns text[] language sql immutable as $$
	select array(select jsonb_array_elements_text($1))
$$;

alter table my_table alter jsonb_col type text[] using jsonb_text_array(jsonb_col)

CREATE FUNCTION

ALTER TABLE

select *
from my_table;

id
jsonb_col

1
{one,two,three}

2
{red,green,blue}

SELECT 2