[Question] Nullxxxx fields with "pgx/v5" and json.Marshal on left join

[polderudo]

Version

1.22.0

What happened?

Using postgres and "pgx/v5" is there any way to specify that any Nullxxx types should json.Marshal to null or the coresponding underlying type?

Doing a left join on the provided example, the status can be null, therefore we get a NullTestStatus type. Question is, if it's possible to specify, that this should be either null or the string itself on marshaling (without using emit_pointers_for_null_types=true of course).

Relevant log output

Will output:

Select:
{"id":1,"status":"none"}

Select via left join:
[{"field1":11,"status":{"test_status":"none","valid":true}},{"field1":22,"status":{"test_status":"new","valid":true}},{"field1":33,"status":{"test_status":"","valid":false}}]

Select via join:
[{"field1":11,"status":"none"},{"field1":22,"status":"new"}]

Database schema

create type test_status as enum('none', 'new');
CREATE TABLE test1
(
    id int not null,
    status test_status not null default 'none'
);

CREATE TABLE test2
(
    field1 int not null,
    test1_status_id int null
);

insert into test1 values(1, 'none');
insert into test1 values(2, 'new');

insert into test2 values(11, 1);
insert into test2 values(22, 2);
insert into test2 values(33, null);

SQL queries

-- name: Test1 :one
select * from test1 where id=$1;

-- name: Test2 :many
select t2.field1, t1.status from test2 t2 left join test1 t1 on t1.id=t2.test1_status_id;

-- name: Test3 :many
select t2.field1, t1.status from test2 t2 join test1 t1 on t1.id=t2.test1_status_id;

Configuration

func TestNulls(t *testing.T) {
    srv, err := core_testing.Init(false)
    if err != nil {
        panic(err)
    }

    q := models.New(db.DB)

    t1, _ := q.Test1(srv.Ctx, 1)
    a, _ := json.Marshal(t1)
    fmt.Println(string(a))

    t2, err := q.Test2(srv.Ctx)
    if err != nil {
        panic(err)
    }
    b, _ := json.Marshal(t2)
    fmt.Println(string(b))

    t3, err := q.Test3(srv.Ctx)
    if err != nil {
        panic(err)
    }
    c, _ := json.Marshal(t3)
    fmt.Println(string(c))
}

Will output:
{"id":1,"status":"none"}

[{"field1":11,"status":{"test_status":"none","valid":true}},{"field1":22,"status":{"test_status":"new","valid":true}},{"field1":33,"status":{"test_status":"","valid":false}}]

[{"field1":11,"status":"none"},{"field1":22,"status":"new"}]

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[andrei-dascalu]

The Null*** fields are part of the sql package so I'm not sure it falls under the purview of sqlc anyway. But the simple workaround is to make those types part of the Marshaller interface yourself. Eg:

func (x sql.NullString) Marshall() ([]byte, error) {
    if !x.Valid {
       return null, nil
    }

   return []byte(x.String), nil
}

I have not tested it by any means :) but I'm guessing that's the way to go as probably the people behind sql package won't want to get too opinionated on how people would want to encode null fields.

[polderudo]

Hello Andrei, yes, thats how we use it right now:

func (f *NullTestStatus) MarshalJSON() ([]byte, error) {
    if f.Valid {
        return json.Marshal(f.TestStatus)
    }

    return json.Marshal(nil)
}

But of course would be nicer to have that automatictly generated.