3 ms·
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.
by brightball 4y ago
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.
- deleted 4y ago[deleted]
- ttfkam 4y agoThis 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.