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
CREATE TABLEstatement?JSONBis just the storage mechanism. You can store a JSON string into a JSONB column directly. You don't need to encode it in advancepgtype.JSONBis passed as a parameter, the PostgreSQL driver sends it in binary protocol mode (\x...), but the database column expects text-formatted JSON input.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.