`pgtype.JSON ERROR: invalid input syntax for type json` with `PreferSimpleProtocol = true`

[buildpeak]

trafficstars

Error message

pgtype.JSON ERROR: invalid input syntax for type json (SQLSTATE 22P02)

Description

When use pgx/v4/stdlib and sqlx to store pgtype.JSON to database, will get an error like above(SQLSTATE 22P02). I also tried database/sql and got the same errors. However, with pgx.ConnectConfig(ctx, connConfig), pgtype.JSON worked but []byte not.

Code to reproduce

package main

import (
    "log"

    "github.com/jackc/pgtype"
    "github.com/jackc/pgx/v4"
    "github.com/jackc/pgx/v4/stdlib"
    "github.com/jmoiron/sqlx"
)

func main() {
    dns := "user=tester host=localhost dbname=tester sslmode=disable"
    connConfig, _ := pgx.ParseConfig(dns)
    connConfig.PreferSimpleProtocol = true
    conn, err := sqlx.Open("pgx", stdlib.RegisterConnConfig(connConfig))
    //conn, err := pgx.ConnectConfig(ctx, connConfig)
    if err != nil {
        log.Fatal(err)
    }

    _, err = conn.Exec(`drop table if exists pgx514;`)
    if err != nil {
        log.Fatal(err)
    }

    _, err = conn.Exec(`create table pgx514 (id serial primary key, data jsonb not null);`)
    if err != nil {
        log.Fatal(err)
    }

    dataJSON := &pgtype.JSON{Bytes: []byte(`{"foo": "bar"}`), Status: pgtype.Present}
    commandTag, err := conn.Exec("insert into pgx514(data) values($1)", dataJSON)
    if err == nil {
        log.Println("pgtype.JSON", commandTag)
    } else {
        log.Println("pgtype.JSON", err)
    }

    dataBytes := []byte(`{"foo": "bar"}`)
    commandTag, err = conn.Exec("insert into pgx514(data) values($1)", dataBytes)
    if err == nil {
        log.Println("[]byte", commandTag)
    } else {
        log.Println("[]byte", err)
    }
}

[jackc]

This is an edge case with the simple protocol and database/sql.

One problem is that when using the simple protocol is the destination PostgreSQL type is unknown. The type has to be determined by completely by the Go side. What is a []byte encoded as? pgx considers it bytea which means it is hex encoded. Use a string for raw JSON data instead of []byte.

The pgtype.JSON case is more complicated. (As an aside, use pgtype.JSONB for PostgreSQL jsonb.) When using the pgx native interface there is a special case to handle pgtype.JSON.

https://github.com/jackc/pgx/blob/eeda0368e66fafed0a3db500108bdb87b657a88a/values.go#L40

However, when using database/sql, pgx can't see that it is a pgtype.JSON, database/sql has already called Value() and converted it to a []byte. And Value() returns []byte for compatibility with lib/pq and json.RawMessage (https://github.com/jackc/pgx/issues/409).

So I'm not sure if this exact case can be solved without breaking something else. But you can use string, sql.NullString, or pgtype.Text instead.

[aethanol]

@jackc do you know if this issue is impacted / resolved with the removal of the JSONB type? https://github.com/jackc/pgx/blob/master/CHANGELOG.md#other-changes

[jackc]

@aethanol It's still an issue with v5. Same fundamental problem though the internal implementation details differ.

One problem is that when using the simple protocol is the destination PostgreSQL type is unknown. The type has to be determined by completely by the Go side. What is a []byte encoded as? pgx considers it bytea which means it is hex encoded. Use a string for raw JSON data instead of []byte.

[asendia]

So I'm not sure if this exact case can be solved without breaking something else. But you can use string, sql.NullString, or pgtype.Text instead.

Thanks for the explanation @jackc.

I solved my issue by doing something like this:

-- schema.sql
CREATE TABLE IF NOT EXISTS my_table(
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    my_data jsonb
);

-- query.sql
-- name: InsertMyTable :exec
INSERT INTO my_table (data)
VALUES ((@my_data::text)::jsonb);

Please let me know if I missed something.