Postgres 14

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?.

drop table if exists tasks;
create table tasks(
    attachments json);
insert into tasks 
    values ('[ "200px-RR5219-0015R.png", "2017_03_25_2235.doc", "137555.jpg" ]');
select * from tasks;

DROP TABLE

CREATE TABLE

INSERT 0 1

attachments

[ "200px-RR5219-0015R.png", "2017_03_25_2235.doc", "137555.jpg" ]

SELECT 1

create or replace function json_to_text_array(json)
returns text[] language sql immutable as $$
    select array_agg(value)
    from json_array_elements_text($1)
$$;

CREATE FUNCTION

alter table tasks alter column attachments type text[] 
    using json_to_text_array(attachments);
select * from tasks;

ALTER TABLE

attachments

{200px-RR5219-0015R.png,2017_03_25_2235.doc,137555.jpg}

SELECT 1