Postgres 14

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 fruit(id bigint primary key, data jsonb);
INSERT INTO fruit
VALUES(1,    
$${
   "type": "pinapple",
   "store1": {
      "first_added": "timestamp A",
      "price": "10",
      "store_id": "1",
      "comments": "some comments..."
   },
   "store2": {
      "first_added": "timestamp B",
      "price": "11",
      "store_id": "2",
      "comments": "some comments..."
   }
}$$);
TABLE fruit;

CREATE TABLE



INSERT 0 1




id
data


1
{"type": "pinapple", "store1": {"price": "10", "comments": "some comments...", "store_id": "1", "first_added": "timestamp A"}, "store2": {"price": "11", "comments": "some comments...", "store_id": "2", "first_added": "timestamp B"}}



SELECT 1


UPDATE fruit
SET data = jsonb_set(data, ARRAY[key], jsonb_set(json_value, '{first_added}', data->key->'first_added'))
FROM (VALUES('store1', '{"price": "12", "store_id": "1", "comments": "some comments...V2"}'::jsonb)) AS vals(key, json_value)
WHERE id = 1;

UPDATE 1


TABLE fruit;


id
data


1
{"type": "pinapple", "store1": {"price": "12", "comments": "some comments...V2", "store_id": "1", "first_added": "timestamp A"}, "store2": {"price": "11", "comments": "some comments...", "store_id": "2", "first_added": "timestamp B"}}



SELECT 1