4 ms·
You don't need JSONB for this - doing this with plain JSON is simpler and already faster than individual inserts for most bindings in my experience. I typicall
by matharmin 3y ago
You don't need JSONB for this - doing this with plain JSON is simpler and already faster than individual inserts for most bindings in my experience.
I typically do bulk inserts using a single JSON argument like this:
WITH ins AS (SELECT e.value ->> 'id', e.value ->> 'name', e.value ->> 'age' FROM json_each(?) e)
INSERT INTO data (id, name, age)
SELECT * FROM ins
The same approach can be used for bulk updates and deletes as well.
- bvrmn 3y agoI have quite wide records (over 50 fields) and ->> performs not well with text keys. I did not try it with array indexing though: WITH ins AS ( SELECT value ->> 0, value ->> 1, value ->> 2 FROM json_each('[["some", "joe", 10], ["uuid", "sam", 20], ["key", "phil", 30]]') ) INSERT INTO data (id, name, value) SELECT * FROM ins