4 ms·
Can't you use json operators [1] for this? -- Permanently delete message sent by jon DELETE from deleted_record where table_name = 'messages' and data->>'send
by username1407 4y ago
Can't you use json operators [1] for this?
-- Permanently delete message sent by jon
DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon';
[1] https://www.postgresql.org/docs/15/functions-json.html https://www.postgresql.org/docs/15/functions-json.html
- duskwuff 4y agoThat assumes that all of a user's data is a single foreign key relationship away from the user, which often isn't the case. It also requires you to scan the entire table and decode JSON for every row you've ever deleted, which may become prohibitively expensive for large databases.
- username1407 4y agoThat's true. To give an example for your 1st point: If you have the schema users->messages->message_attachments and message_attachments does not store the user_id, you'll have trouble finding all attachments from a specific user.
- simonw 4y agoPostgreSQL can create indexes on the results of expressions, so it's possible to accelerate queries that select against a nested value inside a JSON blob.
- andretti1977 4y agoThe deletion table is a schemaless table, it may become hard to anonimize the data