Postgres 12

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 the_table (id int, reaction text);

CREATE TABLE


insert into the_table
values
  (1, 'laugh'),
  (2, 'love'),
  (3, 'love'),
  (4, 'like'),
  (5, 'like'),
  (6, 'surprised'),
  (7, 'like'),
  (8, 'love'),
  (9, 'like'),
  (10, 'surprised');

INSERT 0 10


select jsonb_agg(jsonb_build_object(reaction, count))
from (
  select reaction, count(*)
  from the_table
  group by reaction
) t;


jsonb_agg


[{"surprised": 2}, {"like": 4}, {"laugh": 1}, {"love": 3}]



SELECT 1


select jsonb_agg(array[reaction, "count"])
from (
  select reaction, count(*)::text as "count"
  from the_table
  group by reaction
) t;


jsonb_agg


[["surprised", "2"], ["like", "4"], ["laugh", "1"], ["love", "3"]]



SELECT 1