3 ms·
Postgres's JSONB uses native numbers, which is faster but coerces values. It also removes duplicate keys but you shouldn't use those anyway. Sqlite's JSONB kee
by o11c 3y ago
Postgres's JSONB uses native numbers, which is faster but coerces values. It also removes duplicate keys but you shouldn't use those anyway.
Sqlite's JSONB keeps numbers as strings, which is slower but preserves your weird JSON (since there's no such thing as standard JSON). I'm not sure about duplicate keys.
- lifthrasiir 3y agoSQLite's approach is faster if you are not frequently extracting numerical values out of JSONB. It also makes much easier to convert JSONB back to JSON. I think SQLite JSONB reserved enough space to define a native number type (among others) if this assumption turned out to be false.
- chrismorgan 3y ago> since there's no such thing as standard JSON I-JSON is the most sensible JSON profile I know of: https://datatracker.ietf.org/doc/html/rfc7493 https://datatracker.ietf.org/doc/html/rfc7493. It says: UTF-8 only, prefer not to use numbers beyond IEEE 754-2008 binary64 precision, no duplicate keys, and a couple more things.
- rafaelmn 3y ago> Postgres's JSONB uses native numbers, which is faster but coerces values. You would probably be string quoting your numbers in your data/model if this matters to you so sounds like the right call from PG implementation.