Title: Postgres: casting result to text results in sqlc generating a string instead of a nullable string · Issue #3710 · sqlc-dev/sqlc · GitHub
Open Graph Title: Postgres: casting result to text results in sqlc generating a string instead of a nullable string · Issue #3710 · sqlc-dev/sqlc
X Title: Postgres: casting result to text results in sqlc generating a string instead of a nullable string · Issue #3710 · sqlc-dev/sqlc
Description: 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 sa...
Open Graph Description: 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 th...
X Description: 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 wher...
Opengraph URL: https://github.com/sqlc-dev/sqlc/issues/3710
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Postgres: casting result to text results in sqlc generating a string instead of a nullable string","articleBody":"### Version\r\n\r\n1.27.0\r\n\r\n### What happened?\r\n\r\n`panic: can't scan into dest[2]: cannot scan NULL into *string`\r\n\r\nI receive this error because the result has nulls in it, but SQLC has generated an output struct where the column is not nullable.\r\n\r\nRunning the same query manually:\r\n```sql\r\nSELECT *, (mt.myjson->'thing1'->'thing2')::text FROM \"mytable\" mt;\r\n```\r\n\r\nReturns:\r\n```csv\r\n1\t{}\t\t\t\t\t[NULL]\r\n2\t{\"thing1\": {\"thing2\": \"thing3\"}}\t\"thing3\"\r\n```\r\n\r\n\r\nRunning a golang query using the generated code:\r\n```golang\r\n\trows, err := db.MyGet(ctx)\r\n\tif err != nil {\r\n\t\tpanic(err)\r\n\t}\r\n\r\n\tfor _, row := range rows {\r\n\t\tfmt.Printf(\"%#+v\\n\", row)\r\n\t}\r\n```\r\n\r\nGives:\r\n```\r\npanic: can't scan into dest[2]: cannot scan NULL into *string\r\n```\r\n\r\nThe generated output model:\r\n```golang\r\ntype MyGetRow struct {\r\n\tID int64 `json:\"id\"`\r\n\tMyjson []byte `json:\"myjson\"`\r\n\tColumn3 string `json:\"column_3\"`\r\n}\r\n```\r\n\r\n\r\n**The problem stems from the fact that postgres casts allow null, while SQLC is assuming casts means NOT NULL.**\r\n\r\nI can see that on the parameter/argument side of things, sqlc has `sqlc.narg()` for helping clarify the situation.\r\nHowever, on the generated struct side, there doesn't appear to be anything easy to help fix this situation.\r\n\r\n\r\n### Expectation\r\nThe generated output model should be:\r\n```golang\r\ntype MyGetRow struct {\r\n\tID int64 `json:\"id\"`\r\n\tMyjson []byte `json:\"myjson\"`\r\n\tColumn3 pgtype.Text `json:\"column_3\"`\r\n}\r\n```\r\n\r\n### Relevant log output\r\n\r\n_No response_\r\n\r\n### Database schema\r\n\r\n```sql\r\nCREATE TABLE \"mytable\" (\r\n id BIGSERIAL NOT NULL PRIMARY KEY,\r\n myjson JSONB NOT NULL\r\n);\r\n\r\ninsert into mytable (myjson) values \r\n ('{}'),\r\n ('{\"thing1\": {\"thing2\": \"thing3\"}}');\r\n```\r\n\r\n\r\n### SQL queries\r\n\r\n```sql\r\n-- name: MyGet :many\r\nSELECT *, (mt.myjson->'thing1'->'thing2')::text\r\nFROM \"mytable\" mt;\r\n```\r\n\r\n\r\n### Configuration\r\n\r\n```yaml\r\nversion: \"2\"\r\nsql:\r\n - engine: \"postgresql\"\r\n queries: \"sqlc_queries.sql\"\r\n schema: \"desired_end_state_schema.sql\"\r\n gen:\r\n go:\r\n package: \"models\"\r\n out: \"models\"\r\n sql_package: \"pgx/v5\"\r\n emit_json_tags: true\r\n```\r\n\r\n\r\n### Playground URL\r\n\r\nhttps://play.sqlc.dev/p/50bf69bcc997f524318e1c0fbcf9bc5c5e342c327f6eb2f7901a25756727547d\r\n\r\n### What operating system are you using?\r\n\r\nLinux, macOS\r\n\r\n### What database engines are you using?\r\n\r\nPostgreSQL\r\n\r\n### What type of code are you generating?\r\n\r\nGo","author":{"url":"https://github.com/veqryn","@type":"Person","name":"veqryn"},"datePublished":"2024-11-16T08:04:31Z","url":"https://github.com/sqlc-dev/sqlc/issues/3710"}
| react-profiling | 0 |
| react-import-map | react,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322 |
| route-pattern | /_view_fragments/issues/show/:user_id/:repository/:id/issue_layout(.:format) |
| route-controller | voltron_issues_fragments |
| route-action | issue_layout |
| fetch-nonce | v2:5289521b-3a4e-16c7-b522-13479a05962f |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | C3F4:21AC3F:2C7F6B4:385098D:6B0196B9 |
| html-safe-nonce | 500101c91a5959d62986a5a68a5dcb42b19fe5f58baeff309e335416ea92c23a |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJDM0Y0OjIxQUMzRjoyQzdGNkI0OjM4NTA5OEQ6NkIwMTk2QjkiLCJ2aXNpdG9yX2lkIjoiNTEzODIwMzUwOTgyODcyMDMxMyIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9 |
| visitor-hmac | 0a39807fe6b1d589e39f80be78f8139459abca142b6cba1f6a76156e9790661d |
| hovercard-subject-tag | issue:2663996154 |
| github-keyboard-shortcuts | repository,issues,copilot |
| google-site-verification | Apib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I |
| octolytics-url | https://collector.github.com/github/collect |
| analytics-location | / |
| fb:app_id | 1401488693436528 |
| apple-itunes-app | app-id=1477376905, app-argument=https://github.com/_view_fragments/issues/show/sqlc-dev/sqlc/3710/issue_layout |
| twitter:image | https://opengraph.githubassets.com/743b915cdd6c8fa489be7339ae0984a834adbd1a9ff17e8c7061d4e3d30c65eb/sqlc-dev/sqlc/issues/3710 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/743b915cdd6c8fa489be7339ae0984a834adbd1a9ff17e8c7061d4e3d30c65eb/sqlc-dev/sqlc/issues/3710 |
| og:image:alt | 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 th... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | veqryn |
| hostname | github.com |
| expected-hostname | github.com |
| None | cc5dfdd32f62ebd3223cab3af3aa2b268d3605218237326a48a99d1efd89495c |
| turbo-cache-control | no-preview |
| go-import | github.com/sqlc-dev/sqlc git https://github.com/sqlc-dev/sqlc.git |
| octolytics-dimension-user_id | 136738596 |
| octolytics-dimension-user_login | sqlc-dev |
| octolytics-dimension-repository_id | 193160679 |
| octolytics-dimension-repository_nwo | sqlc-dev/sqlc |
| octolytics-dimension-repository_public | true |
| octolytics-dimension-repository_is_fork | false |
| octolytics-dimension-repository_network_root_id | 193160679 |
| octolytics-dimension-repository_network_root_nwo | sqlc-dev/sqlc |
| turbo-body-classes | logged-out env-production page-responsive |
| disable-turbo | false |
| browser-stats-url | https://api.github.com/_private/browser/stats |
| browser-errors-url | https://api.github.com/_private/browser/errors |
| release | 8c77c9e8439031ad54c75a250694842892f693e1 |
| ui-target | full |
| theme-color | #1e2327 |
| color-scheme | light dark |
| react-app-name | issues |
Links:
Viewport: width=device-width