Postgres: casting result to text results in sqlc generating a string instead of a nullable string

Version

1.27.0

What happened?

panic: can't scan into dest[2]: cannot scan NULL into *string

I receive this error because the result has nulls in it, but SQLC has generated an output struct where the column is not nullable.

Running the same query manually:

SELECT *, (mt.myjson->'thing1'->'thing2')::text FROM "mytable" mt;

Returns:

1   {}                  [NULL]
2   {"thing1": {"thing2": "thing3"}}    "thing3"

Running a golang query using the generated code:

    rows, err := db.MyGet(ctx)
    if err != nil {
        panic(err)
    }

    for _, row := range rows {
        fmt.Printf("%#+v\n", row)
    }

Gives:

panic: can't scan into dest[2]: cannot scan NULL into *string

The generated output model:

type MyGetRow struct {
    ID      int64  `json:"id"`
    Myjson  []byte `json:"myjson"`
    Column3 string `json:"column_3"`
}

The problem stems from the fact that postgres casts allow null, while SQLC is assuming casts means NOT NULL.

I can see that on the parameter/argument side of things, sqlc has sqlc.narg() for helping clarify the situation. However, on the generated struct side, there doesn't appear to be anything easy to help fix this situation.

Expectation

The generated output model should be:

type MyGetRow struct {
    ID      int64  `json:"id"`
    Myjson  []byte `json:"myjson"`
    Column3 pgtype.Text `json:"column_3"`
}

Relevant log output

No response

Database schema

CREATE TABLE "mytable" (
    id                BIGSERIAL   NOT NULL PRIMARY KEY,
    myjson             JSONB       NOT NULL
);

insert into mytable (myjson) values 
    ('{}'),
    ('{"thing1": {"thing2": "thing3"}}');

SQL queries

-- name: MyGet :many
SELECT *, (mt.myjson->'thing1'->'thing2')::text
FROM "mytable" mt;

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "sqlc_queries.sql"
    schema: "desired_end_state_schema.sql"
    gen:
      go:
        package: "models"
        out: "models"
        sql_package: "pgx/v5"
        emit_json_tags: true

Playground URL

https://play.sqlc.dev/p/50bf69bcc997f524318e1c0fbcf9bc5c5e342c327f6eb2f7901a25756727547d

What operating system are you using?

Linux, macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

I have tried changing my query to the json operator to specifically extract as text:

-- name: MyGet :many
SELECT *, mt.myjson->'thing1'->>'thing2'
FROM "mytable" mt;

This results in an output model using interface{}, which is just as bad:

type MyGetRow struct {
    ID      int64  `json:"id"`
    Myjson  []byte `json:"myjson"`
    Column3 interface{} `json:"column_3"`
}

This is despite the docs saying that the ->> operator outputs text: https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING

I have also tried adding a case statement to try to make it clear to SQLC that the result can be null:

-- name: MyGet :many
SELECT *, CASE WHEN mt.myjson @? '$.thing1.thing2' THEN (mt.myjson->'thing1'->'thing2')::text ELSE null::text END
FROM "mytable" mt;

When using ELSE null::text, the result is an output model using type string When using either ELSE null or just leaving out the ELSE part, the result is an output model using type interface{}

I have also tried switching the type from a text to an enum, but the results are the same.

To be clear, my goal is to have the output model be:

type MyGetRow struct {
    ID      int64  `json:"id"`
    Myjson  []byte `json:"myjson"`
    Column3 pgtype.Text `json:"column_3"`
}

I definitely thought that null::text should have resulted in a nullable string being generated..., but it didn't.

sqlc-dev / sqlc