Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

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.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: