Failing to scan rows into map[string]interface{} with null values

[cemremengu]

• How you call scany API : using pgxscan (v2) with pgxpool (v5)

First of all thanks for this library!

Example of the code you could provide to aid debugging:

var rows []map[string]interface{}
err = pgxscan.Select(context.Background(), db, &rows, q, args...)
fmt.Println(err)
fmt.Println(rows)

However, it fails with the following error when there is a null column.

 doing scan: scanFn: scany: scan rows into map: can't scan into dest[4]: cannot scan NULL into *interface {}

I expected null values can be used with the interface{} type without needing pointers I don't know the result structure of the query in advance so what should I do to achieve this?

[georgysavva]

Hi @cemremengu. Sorry for the late response but I can't reproduce the issue. The following code works for me:

    var rows []map[string]interface{}
    conn, _ := pgx.Connect(context.Background(), ts.PGURL().String())
    err=pgxscan.Select(context.Background(), conn, &rows, `SELECT *
    FROM (
        VALUES ('foo val'),  (NULL)
    ) AS t ("foo")`)
    if err != nil {
        panic(err)
    }

    log.Printf("%+v", rows)

It prints:

[map[foo:foo val] map[foo:<nil>]]

Can you provide more details?

[cemremengu]

Hi @georgysavva sorry for even later response 😞

Looks like null jsonb columns cause the problem. If you create a table like below you can see that the following code fails. Is this expected?

CREATE TABLE test (
    a int4 NULL,
    b int4 NULL,
    c jsonb NULL
);

INSERT INTO test (a, b, c) VALUES (1, null, null);

package main

import (
    "context"
    "log"

    "github.com/georgysavva/scany/v2/pgxscan"
    "github.com/jackc/pgx/v5"
)

func main() {
    var rows []map[string]interface{}
    conn, _ := pgx.Connect(context.Background(), , ts.PGURL().String())

    // this will fail with can't scan into dest[0]: cannot scan NULL into *interface {}
    err := pgxscan.Select(context.Background(), conn, &rows, `SELECT c from test`) 

    // this works
    // err = pgxscan.Select(context.Background(), conn, &rows, `SELECT a,b from test`)

    if err != nil {
        panic(err)
    }

    log.Printf("%+v", rows)
}

[andersarpi]

For what it's worth, I just stumbled upon the same issue after updating to v2. The code worked fine with v1. The offending field is, just like in @cemremengu's case above, a nullable jsonb column being scanned into an value of type any/interface{}.

A workaround for me was to change the value into a map[string]any but that of course won't always work. It will probably also fail if the jsonb column contains an array at the top level.

[georgysavva]

Thanks for your feedback, guys! I am sorry I couldn't look into this yet. However, I will do it soon and let you know!

[andersarpi]

Take your time, man! If it’s important enough for any of us we can always help with the issue instead, right? :-)

[georgysavva]

I've looked closer into this. To use a map destination with possible NULL values, you need to define your map like this:

var rows []map[string]*interface{}

It needs an extra pointer layer to accommodate possible NULLs. Then it works for all column types, including jsonb. It's not specific to scany. It's just how pgx handles NULLable destinations.

Here is how you process your new map destination type after scanning into it:

    for _,row := range rows{
        a:=row["a"]
        if a != nil{
            fmt.Println("a",(*a).(int))
        }
        c:=row["c"]
        if c != nil{
            fmt.Println("c",(*c).(map[string]interface{}))
        }
      }

That extra pointer pattern isn't something specific to interface{}. Here is an extra example. Imagine that all your columns are of type INT with possible NULL values, and we want to scan them into a map. Here is how we would need to define it:

var rows []map[string]*int

We have the same extra pointer layer here to allow pgx to store NULL into the*int type because it can't put a NULL into the int type.

I hope this helps. Let me know if you have any further questions.

[cemremengu]

Thanks for the info, as you point out the issue is actually caused by pgx mapping.

With the patch I submitted to pgx it should hopefully work as expected. Until the next release, any or a pointer as you suggested works well.

Feel free to close this or wait until the nexr release

[georgysavva]

Cool. Closing this one, then. Feel free to reopen