Repository navigation
[Question] Nullxxxx fields with "pgx/v5" and json.Marshal on left join #2817
Description
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
Activity
andrei-dascalu commentedon Oct 9, 2023
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:
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 commentedon Oct 10, 2023
Hello Andrei, yes, thats how we use it right now:
But of course would be nicer to have that automatictly generated.