Postgres 10

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 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit


CREATE TABLE test
( id    BIGSERIAL
, attr  JSONB
);
INSERT INTO test
(attr)
VALUES
('{"pe_cd":"07","me_cd":"006","mf":"4.0","mfm":"4.0","pot":null,"earliestDate":[{"date":"2019-07-01","_destroy":false},{"date":"2020-01-01","_destroy":""}],"earliestDate2":null,"leavingDate":null}'::JSONB)
;
1 rows affected

SELECT * FROM test;


id
attr


1
{"mf": "4.0", "mfm": "4.0", "pot": null, "me_cd": "006", "pe_cd": "07", "leavingDate": null, "earliestDate": [{"date": "2019-07-01", "_destroy": false}, {"date": "2020-01-01", "_destroy": ""}], "earliestDate2": null}


SELECT *
  FROM test
 WHERE attr->>'earliestDate2' IS NULL
;


id
attr


1
{"mf": "4.0", "mfm": "4.0", "pot": null, "me_cd": "006", "pe_cd": "07", "leavingDate": null, "earliestDate": [{"date": "2019-07-01", "_destroy": false}, {"date": "2020-01-01", "_destroy": ""}], "earliestDate2": null}


WITH null_dates AS
(
    SELECT id, (jsonb_array_elements(attr->'earliestDate')->>'date')::DATE as d
      FROM test
     WHERE attr->>'earliestDate2' IS NULL
)
UPDATE test t
   SET attr = attr||jsonb_build_object('earliestDate2', sub.edate)
  FROM
(
SELECT id, MIN(d) AS edate
  FROM null_dates
 GROUP BY id
) sub
WHERE t.id = sub.id
;
1 rows affected

SELECT attr->>'earliestDate2' FROM test;


?column?


2019-07-01