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 |