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 mytable (
    mydata jsonb
);

INSERT INTO mytable VALUES ('[{"a":[],"c_id":624,"ps":[{"":0,"pr":"73","f":"M","s":"M"}],"g_n":"K L Mish","g_num":1},{"":[],"c_id":719,"ps":[{"a":0,"pr":"65433","f":"R","s":"W"}],"g_n":"S H Star","g_num":2}]');
1 rows affected

SELECT
     regexp_replace(mydata::text, '(.*)("c_id"\s*:\s*)(719)(.*)','\1\2720\4') AS result
FROM
    mytable


result


[{"a": [], "ps": [{"": 0, "f": "M", "s": "M", "pr": "73"}], "g_n": "K L Mish", "c_id": 624, "g_num": 1}, {"": [], "ps": [{"a": 0, "f": "R", "s": "W", "pr": "65433"}], "g_n": "S H Star", "c_id": 720, "g_num": 2}]


UPDATE mytable
SET mydata = s.result::jsonb
FROM (
    SELECT
        regexp_replace(mydata::text, '(.*)("c_id"\s*:\s*)(719)(.*)','\1\2720\4') AS result
    FROM
        mytable
) s;

SELECT * FROM mytable
1 rows affected



mydata


[{"a": [], "ps": [{"": 0, "f": "M", "s": "M", "pr": "73"}], "g_n": "K L Mish", "c_id": 624, "g_num": 1}, {"": [], "ps": [{"a": 0, "f": "R", "s": "W", "pr": "65433"}], "g_n": "S H Star", "c_id": 720, "g_num": 2}]