Search code examples
arrayspostgresqlgojsonbpgx

"wrong element type" when using JSONBArray using pgx

I am trying to insert a new row that has an inventory with data type jsonb[]:

elements := []pgtype.Text{{String: `{"adsda": "asdasd"}`, Status: pgtype.Present}}
dimensions := []pgtype.ArrayDimension{{Length: 1, LowerBound: 1}}
inventory := pgtype.JSONBArray{Elements: elements, Dimensions: dimensions, Status: pgtype.Present}
row = db.pool.QueryRow(context.Background(), `INSERT INTO user ("email", "password", "inventory") VALUES($1, $2, $3) RETURNING uuid, email, "password"`, requestEmail, requestPassword, inventory)

But I get the following error:

"Severity": "ERROR",
"Code": "42804",
"Message": "wrong element type",
"Detail": "",
"Hint": "",
"Position": 0,
"InternalPosition": 0,
"InternalQuery": "",
"Where": "",
"SchemaName": "",
"TableName": "",
"ColumnName": "",
"DataTypeName": "",
"ConstraintName": "",
"File": "arrayfuncs.c",
"Line": 1316,
"Routine": "array_recv"

Postgres table definition:

CREATE TABLE public.user (
    uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
    email varchar(64) NOT NULL,
    "password" varchar(32) NOT NULL,
    inventory _jsonb NULL,
    CONSTRAINT user_pk PRIMARY KEY (uuid)
);

What might be the issue? Any idea would help.

Solution

  • Type jsonb[] in pgx was broken

    As for the error message you report:

    "Severity": "ERROR",
"Code": "42804",
"Message": "wrong element type",
...

    The Guthub page on pgx reveals:

    The binary format can be substantially faster, which is what the pgx interface uses.

    So you are using the binary protocol. For this, the data types have to use a compatible binary format, and it seems that ARRAY of jsonb is not encoded properly? Related:

    Luckily for you, the author seems to have fixed this just yesterday: (!)

    jackc: Fix JSONBArray to have elements of JSONB

    Your problem should go away once you install the latest version containing commit 79b05217d14ece98b13c69ba3358b47248ab4bbc

    jsonb[] vs. jsonb with nested JSON array

    It might be simpler to use a plain jsonb instead of jsonb[]. JSON can nest arrays by itself. Consider:

    SELECT '[{"id": 1}
       , {"txt": "something"}]'::jsonb  AS jsonb_array
     , '{"{\"id\": 1}"
        ,"{\"txt\": \"something\"}"}'::jsonb[] AS pg_array_of_jsonb;

    Either can be unnested in Postgres:

    SELECT jsonb_array_elements('[{"id": 1}, {"txt": "something"}]'::jsonb) AS jsonb_element_from_json_array;

SELECT unnest('{"{\"id\": 1}","{\"txt\": \"something\"}"}'::jsonb[]) AS jsonb_element_from_pg_array;

    Same result.

    db<>fiddle here

    That should also avoid your error.

    Additional error

    Your INSERT command:

    INSERT INTO user ("email", "password", "inventory") VALUES ...

    ... should really raise this:

    ERROR: syntax error at or near "user"

    Because user is a reserved word. You would have to double-quote it to make it work. But rather don't use user it as Postgres identifier. Ever.

    The table creation works because there the tablename is schema-qualified, which makes it unambiguous:

    CREATE TABLE public.user ( ...