I'd, of course, love to be wrong about this, but when I looked into it about six months ago, the best solution I could find was a hack that had untested performance characteristics. While in Mongo, it's a very simple, standard operation.
I'd, of course, love to be wrong about this, but when I looked into it about six months ago, the best solution I could find was a hack that had untested performance characteristics. While in Mongo, it's a very simple, standard operation.
https://www.postgresql.org/docs/current/static/functions-jso...
From the docs for the `||` operator:
> Concatenate two jsonb values into a new jsonb value
And for `jsonb_set`:
> Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true ( default is true) and the item designated by path does not exist. As with the path orientated operators, negative integers that appear in path count from the end of JSON arrays.
You'd use this function in conjunction with an `UPDATE` statement to get the result that you want.
If you're going to be incrementing a field infrequently, then just resaving the JSONB shouldn't be a big deal. If you're updating a field frequently, having it in it's own column makes more sense and should absolutely be worth the time. It's tremendously more efficient to increment a typed number than it is to increment a piece of a serialized chunk of JSON.
When you create a column with postgres that has a null default, it doesn't allocate space for it. It's one of the things that makes postgres so good for huge databases because adding an empty column is an instantaneous operation, rather than a locking operation. On the flip side, when you create a field in JSON you're duplicating the space used just to name the field in every single row.
But the short version is, if you're updating something often enough that you're willing to fight around upserting into a JSON column rather that just creating that same column it might be worth it to take a step back and re-evaluate why doing it that way is important to you.
Yes. When did I say the opposite?