3 ms·
I'm curious, any chance you remember some of those json/jsonb update hassles? (not arguing, just curious - when things get hairy in JSON, I give up on SQL and
by asah 6y ago
I'm curious, any chance you remember some of those json/jsonb update hassles?
(not arguing, just curious - when things get hairy in JSON, I give up on SQL and [1] I write a user defined function (CREATE FUNCTION) in JS (plv8) or Python (plpython).
[1] assuming the update code needs to run inside the database, e.g. for performance reasons... otherwise just perform your update in the application, where you presumably have a richer library for manipulating data structures...
- jiripospisil 6y agoI don't remember the specific case (it's been a few years) but I do remember it had something to do with updating an array member. I googled around and found this [0] (the second question) which looks very similar. It's as simple as it gets - find an array member with "value" equal to "blue" and decrease its "qty". In MongoDB you can do that pretty easily ([1]) and the update should be atomic. The SQL version looks complicated and it's not even the form that you should use (notice the note about the race condition). Then again, maybe there's already a way to do that in PostgreSQL in a more elegant way, I assume the support has improved over the years. [0] https://dba.stackexchange.com/questions/193390/update-nth-element-of-array-using-a-where-clause https://dba.stackexchange.com/questions/193390/update-nth-el... [1] https://gist.github.com/jiripospisil/36f37a41f9b2c7fe74e841eb064be9d7 https://gist.github.com/jiripospisil/36f37a41f9b2c7fe74e841e...