Easy, alternative soft deletion: `deleted_record_insert`

Re:Easy, alternative soft deletion: `deleted_record_insert` by ducaale on Sunday January 01, 2023 @02:39AM (#34204433)

or you can create view for every table that supports soft deletion and ensure all of your read-only queries are using those table

    CREATE VIEW current_customers AS
        SELECT * FROM customers where deleted_at is null;

    SELECT * FROM current_customers JOIN ...

Of course, this comes with its downsides e.g. views need to be recreated in every migration and there might be some complex join operations that might not work.

[ Parent | on HN ]

Re:Easy, alternative soft deletion: `deleted_record_insert` by chewyfruitloop on Sunday January 01, 2023 @07:19AM (#34205740)

It may be work sometime in the future, but having to prefix every query with a null check sounds bonkers. A view can be optimised to do all that for you. I made the what the flip expression reading that they did a prefix on everything.

[ Parent | on HN ]

Re:Easy, alternative soft deletion: `deleted_record_insert` by Inviz on Sunday January 01, 2023 @08:40AM (#34206176)

It can be the other way around, that the table has prefix/suffix, and the view doesnt. Alternatively view can be created with the same name, but in a different schema, which is set with higher priority for the user (e.g. via search_path in PG)

[ Parent | on HN ]