Postgres 15

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 users (
  settings jsonb
);

-- note the second object with "id": 101
INSERT INTO users VALUES
('[{
         "id": 100,
         "name": "testOne",
         "settings": "settingOne"
     },
     {
         "id": 101,
         "name": "testTwo",
         "settings": "settingTwo"
     },
     {
         "id": 101,
         "name": "testThree",
         "settings": "settingThree"
     }]')
RETURNING *;

CREATE TABLE




settings


[{"id": 100, "name": "testOne", "settings": "settingOne"}, {"id": 101, "name": "testTwo", "settings": "settingTwo"}, {"id": 101, "name": "testThree", "settings": "settingThree"}]



INSERT 0 1


UPDATE users u
SET    settings = (SELECT jsonb_agg(a.elem)
                   FROM   jsonb_array_elements(u.settings) AS a(elem)
                   WHERE  (a.elem ->> 'id' = '101') IS NOT TRUE)
WHERE  u.settings @> '[{"id":101}]';

UPDATE 1


TABLE users;


settings


[{"id": 100, "name": "testOne", "settings": "settingOne"}]



SELECT 1