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

select version();

version

PostgreSQL 16.0 (Debian 16.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

SELECT 1

CREATE FUNCTION jsonb_update (input jsonb) RETURNS jsonb LANGUAGE sql AS
$$
  SELECT json_objectagg(j.key :: text : 
              CASE 
                WHEN jsonb_typeof(j.value) = 'object' THEN jsonb_update(j.value)
                WHEN j.key :: text ~ '^number' THEN to_jsonb(123)
                WHEN j.value :: text ~ '^"http:// 139.168.6.44' THEN to_jsonb(replace (j.value :: text, 'http:// 139.168.6.44', 'http:// 139.168.6.55'))
                ELSE j.value
              END)
    FROM jsonb_each(input) AS j(key,value) ;
$$ ;

CREATE FUNCTION

SELECT jsonb_update('{"number_01":14322,"number_98":629578,"file":"http:// 139.168.6.44/upload", "nested_object":{"number_01":14322,"number_98":629578,"file":"http:// 139.168.6.44/upload"}}' :: jsonb)

jsonb_update

{"file": ""http:// 139.168.6.55/upload"", "number_01": 123, "number_98": 123, "nested_object": {"file": ""http:// 139.168.6.55/upload"", "number_01": 123, "number_98": 123}}

SELECT 1