3 ms·
The clue is in how the query is written: UPDATE characters SET jsonb_column = jsonb_set( jsonb_column, '{superpower}', '"invisi
by simonw 3y ago
The clue is in how the query is written:
UPDATE characters
SET jsonb_column = jsonb_set(
jsonb_column,
'{superpower}',
'"invisibility"'
)
WHERE id = 5;
Or:
UPDATE characters
SET jsonb_column = jsonb_column ||
'{"superpower": "invisibility"}'
WHERE id = 5;
In both cases the SET is a pretty strong clue that the entire value will be overwritten.
I have immense respect for the PostgreSQL development team but I still can't imagine how they would optimize that not to be a rewrite of the stored JSON value.
- wild_egg 3y agoI'm not sure if it's just sugar but PostgreSQL does allow subscripting on JSONB in UPDATE statements UPDATE characters SET jsonb_column['superpower'] = '"invisibility"' WHERE id = 5; Can't seem to find any docs around whether that still does a full rewrite under the hood or not but seemed like an important omission from your examples