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

[veqryn]

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

[veqryn]

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.

[swallowstalker]

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

[veqryn]

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

[swallowstalker]

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