JSONB Is Not a Portable Blob

SQLite and PostgreSQL both use the name JSONB. SQLite says its on-disk format differs from PostgreSQL’s and the two are not binary compatible. Copying stored JSONB bytes between the engines will not transfer the JSON value.

What each engine stores

SQLite normally stores JSON as text. Its JSONB option stores SQLite’s internal parse tree as a BLOB. SQLite’s JSON functions can read that BLOB without parsing text again. The documentation says to treat the format as opaque and keep it inside SQLite. Most SQLite JSONB operations still have linear time complexity. SQLite documents these properties.

PostgreSQL stores jsonb in its own decomposed binary format. It can index jsonb and avoids reparsing the original JSON text for each operation. These properties describe PostgreSQL’s type; SQLite explicitly rules out binary compatibility with its format. PostgreSQL describes its storage and indexing.

Text is the transfer boundary

Move JSON text between the engines, then let the receiving engine build its own binary representation. SQLite’s json(value) returns JSON text from valid JSON text or a JSONB BLOB. Its jsonb(value) builds SQLite JSONB from valid JSON text. PostgreSQL accepts textual JSON input for jsonb, and a jsonb value can be rendered as text. SQLite documents both functions; PostgreSQL shows text input and output.

Text transfer does not guarantee that the original spelling survives. PostgreSQL jsonb drops insignificant whitespace and object key order. If an input object repeats a key, it keeps only the last value. PostgreSQL also rejects some inputs that other JSON readers may accept, including \u0000 escapes. These rules are in PostgreSQL’s JSON type documentation.

What to do

1. Export values as JSON text, using SQLite’s json(value) or PostgreSQL’s jsonb text output. SQLite and PostgreSQL document those paths.
2. Bind that text as input and parse it with the destination engine’s JSON functions or jsonb input. Let the destination produce its own stored format. SQLite and PostgreSQL describe those inputs.
3. Check transferred values that contain repeated keys or unusual escapes against the destination’s rules before relying on a round trip. PostgreSQL documents the relevant changes and rejections.