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 TABLE example(foo) AS (VALUES
  ('{"kws": {}, "distinct": 0, "total_count": 0}'::jsonb),
  ('{"kws": [], "distinct": 1}')
);
TABLE example;

SELECT 2

foo

{"kws": {}, "distinct": 0, "total_count": 0}

{"kws": [], "distinct": 1}

SELECT 2

UPDATE example
SET foo = (
  SELECT jsonb_object_agg(CASE key WHEN 'total_count' THEN 'counter' ELSE key END, value)
  FROM jsonb_each(foo)
)
WHERE foo ? 'total_count';

UPDATE 1

TABLE example;

foo

{"kws": [], "distinct": 1}

{"kws": {}, "counter": 0, "distinct": 0}

SELECT 2