Another is the support case “Help! I accidentally deleted the wrong thing!” where it saves a huge amount of time compared to loading up a full DB backup.
This is precisely the situation the article solves but without repeating
AND NOT is_deleted
after every DB query in every app accessing the database. No full DB backup/restore needed.
INSERT INTO mytable
SELECT recovered.\*
FROM myaudittable audit
, jsonb_populate_record(
audit.jsoncolumn
, NULL::mytable
) recovered
WHERE audit.id = 8675309
-- optionally merge if new data added
ON CONFLICT DO UPDATE
SET field1 = EXCLUDED.field1
, field2 = …etc…
Postgres has a lot of great functionality making jsonb manipulation relatively simple and easy. Is it more complicated than a simple UPDATE? Yes, but you only have that complexity once rather than in every query on the table from every app and ORM and that recovery can be more nuanced since not every restore strategy is equally valid in every situation.