#3710·sqlc

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

Author: veqrynCreated Nov 16, 2024Updated Aug 1, 2026

Labelsbug:books: postgresql:wrench: golang:computer: darwin:computer: linux

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:

sql

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

Returns:

csv

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

sql

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

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

SQL queries

sql

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

Configuration

yaml

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?

Source: sqlc-dev/sqlc

View original on GitHub View discussion on GitHub