Storing JSON in Postgres as BYTEA or other types

Question

I need to have a generic key-value data-store in Postgres and Sqlite3. I originally planned to store this as a JSONB type but the SQL statements I execute needs to be compatible with both Postgres and Sqlite3. Since sqlite doesn't support jsonb, I planned to use BYTEA instead. So something like this:

Originally:

CREATE TABLE foo (
    id INT PRIMARY KEY,
    data JSONB
)

Now

CREATE TABLE foo (
    id INT PRIMARY KEY,
    data BYTEA
)

This is not the best but it's still fine since I thought I could just cast BYTEA to JSON during query. That is, I thought I could do something like this:

SELECT data::json ->> 'name' AS name from foo where id = $1;

But looks like I cannot cast BYTEA to JSON type (ERROR: cannot cast type bytea to json). This post describes how to convert BYTEA to JSON. But I'm worried that the decode() and convert_from() functions are going to be expensive.

So now I'm considering storing this data as VARCHAR instead of BYTEA, which eliminates the need for the decode() and convert_from() functions.

I'm wondering what the best option is here and if there's any better methods given the constraints I described.

Answer 1

I have same setup and successfully extracted data with

SELECT encode(data, 'escape')::jsonb->'name'
FROM   foo