Postgres 16

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 schema myschema;
create table myschema."user"(username, roles) as values
  ('ted','["ROLE_1","ROLE_2"]'::json);

CREATE SCHEMA

SELECT 1

INSERT INTO myschema."user" (username, roles) 
VALUES ('brk', ARRAY['ROLE_USER', 'ROLE_ADMIN', 'ROLE_SUPER_ADMIN']::json)
RETURNING *;

ERROR:  cannot cast type text[] to json
LINE 2: ... ARRAY['ROLE_USER', 'ROLE_ADMIN', 'ROLE_SUPER_ADMIN']::json)
                                                                ^

INSERT INTO myschema."user" (username, roles) 
VALUES ('brk', to_json(ARRAY['ROLE_USER', 'ROLE_ADMIN', 'ROLE_SUPER_ADMIN']))
RETURNING *;

username
roles

brk
["ROLE_USER","ROLE_ADMIN","ROLE_SUPER_ADMIN"]

INSERT 0 1

INSERT INTO myschema."user" (username, roles) 
VALUES ('brk', '["ROLE_USER","ROLE_ADMIN","ROLE_SUPER_ADMIN"]')
RETURNING *;

username
roles

brk
["ROLE_USER","ROLE_ADMIN","ROLE_SUPER_ADMIN"]

INSERT 0 1