0

I'm using Go with sqlx and pgtype.JSONB to store JSON data in PostgreSQL, but the data is being transmitted as hex-encoded bytes instead of regular JSON text, causing "invalid input syntax for type json" errors.

type Platform struct {
    Logo               pgtype.JSONB `db:"logo"`
    Motivation  pgtype.JSONB `db:"motivation"`
    Rules       pgtype.JSONB `db:"rules"`
}

func convertToJSONB(dest *pgtype.JSONB, src interface{}) error {
    data, err := json.Marshal(src)
    if err != nil {
        return err
    }
    return dest.Set(data) // or direct assignment: dest.Bytes = data; dest.Status = pgtype.Present
}

The SQL query shows hex-encoded data instead of JSON: logo = '\x7b2264656661756c74223a22687474707...'

Which decodes to valid JSON: {"default":"https://..."}

ERROR: invalid input syntax for type json at character 435
DETAIL: Token "\" is invalid

What I've tried:

Using dest.Set(data)
Direct assignment: dest.Bytes = data; 
dest.Status = pgtype.Present

Both approaches result in the same hex encoding

Questions:

Why is pgtype.JSONB being transmitted as hex instead of JSON text? Is this a driver issue with how sqlx handles pgtype.JSONB? Should I use *string fields instead for JSON columns? What's the correct way to store JSON data in PostgreSQL JSONB columns using Go?

Environment:

Go with sqlx and github.com/jackc/pgtype PostgreSQL with JSONB columns Data is valid JSON when decoded from hex

6
  • 1
    The hex-encoded string isn't a problem. The B in JSONB means binary. Not text. If the code doesn't create a SQL query with a binary query parameter, it must encode the binary value somehow. The real concern is why doesn't the code create a parameterized query? Commented Aug 30 at 14:40
  • 1
    Where's the code that calls the database? What's the actual table schema, ie CREATE TABLE statement? JSONB is just the storage mechanism. You can store a JSON string into a JSONB column directly. You don't need to encode it in advance Commented Aug 30 at 14:46
  • @PanagiotisKanavos The problem is a type mismatch: when pgtype.JSONB is passed as a parameter, the PostgreSQL driver sends it in binary protocol mode (\x...), but the database column expects text-formatted JSON input. Commented Aug 30 at 14:48
  • 1
    That's what you assumed. The assumption is wrong, and not just because JSONB is binary. The driver doesn't require JSONB. You didn't post any code that actually stores anything to a database. Check this possibly duplicate question - you can easily write update customer set contact = '{ "phones":[....}' to store a JSON string into a JSONB column. If you use query parameters (which sqlx calls bindvars) you won't have to encode anything. Commented Aug 30 at 15:14
  • Have you checked the Exec docs? You can pass values directly, no need to escape anything. This isn't string replacement, the actual values are passed by the driver to the database outside the query itself. The database will compile the SQL query into an execution plan with parameters that get values from the driver parameters. Commented Aug 30 at 15:23

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.