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?

Go

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.

hi, I have created PR to allow any selected column with json operator in type cast to be nullable column. let me know if you have any comments on that. is it working on your side?

#3739

I think the problem is more than just JSON operators, though your PR would help quite a bit.

The following cases should all generate a function that returns []pgtype.Text.
https://play.sqlc.dev/p/fba08f07ca2fed93313e4f005a64330c09b3722d78b868e94031ef774dc885a2

-- name: GetNullable1 :many
SELECT null::text 
FROM "mytable";

-- name: GetNullable2 :many
SELECT CASE WHEN true THEN 'hello'::text ELSE null::text END 
FROM "mytable";

-- name: GetNullable3 :many
SELECT CASE WHEN true THEN 'hello'::text ELSE null END 
FROM "mytable";

-- name: GetNullable4 :many
SELECT CASE WHEN true THEN 'hello'::text END 
FROM "mytable";

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

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

The first (SELECT null::text) does give us []pgtype.Text

But the second one (SELECT CASE WHEN true THEN 'hello'::text ELSE null::text END) gives us []string

And the next two, (SELECT CASE WHEN true THEN 'hello'::text ELSE null END and SELECT CASE WHEN true THEN 'hello'::text END), give us []interface{}, which is bad too.

The last ones your PR might cover.
(mt.myjson->'thing1'->'thing2')::text is returning []string incorrectly

And mt.myjson->'thing1'->>'thing2' is returning []interface{}

Those 6 above would be the test cases I would like added to this library. All should return []pgtype.Text

Really appreciate your feedback. I have added your test cases and few other test cases to make sure that it's really working. Let me know if you have any comments on the PR #3739

This use case is really important!
When sqlc cannot guess the correct type, there's no way around it. One can't even use type casts like ::text because it implicitly means "not null"!

Would be lovely if sqlc supported type casts with "nullable" hint

Rust's sqlx library handles this using the field names themselves. It is a little hacky but it allows defining a column as nullable or not by respectively naming it AS "column?" AS "column!". In that case it is needed because postgres' nullability logic isn't always flawless (coalesce returns nullable regardless of what you give it, for example).

Navigation Menu

Repository navigation

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

Version

What happened?

Expectation

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

Version

What happened?

Expectation

Relevant log output

Database schema

SQL queries

Configuration

Playground URL

What operating system are you using?

What database engines are you using?

What type of code are you generating?

Activity

veqryn commented on Nov 16, 2024

swallowstalker commented on Dec 10, 2024

veqryn commented on Dec 10, 2024

swallowstalker commented on Dec 12, 2024

kolypto commented on Jan 20, 2026

sidju commented on Jul 30, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions