Title: sqlc should treat PostgreSQL function return values as nullable (e.g. *string) to avoid scan errors · Issue #4121 · sqlc-dev/sqlc · GitHub
Open Graph Title: sqlc should treat PostgreSQL function return values as nullable (e.g. *string) to avoid scan errors · Issue #4121 · sqlc-dev/sqlc
X Title: sqlc should treat PostgreSQL function return values as nullable (e.g. *string) to avoid scan errors · Issue #4121 · sqlc-dev/sqlc
Description: 📌 Problem When using sqlc v1.30.0 with a PostgreSQL function that returns a nullable scalar type like TEXT, sqlc generates non-pointer types (e.g. string), which causes runtime errors when the function returns NULL. 💥 Example Given the f...
Open Graph Description: 📌 Problem When using sqlc v1.30.0 with a PostgreSQL function that returns a nullable scalar type like TEXT, sqlc generates non-pointer types (e.g. string), which causes runtime errors when the func...
X Description: 📌 Problem When using sqlc v1.30.0 with a PostgreSQL function that returns a nullable scalar type like TEXT, sqlc generates non-pointer types (e.g. string), which causes runtime errors when the func...
Opengraph URL: https://github.com/sqlc-dev/sqlc/issues/4121
X: @github
Domain: github.com
{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"sqlc should treat PostgreSQL function return values as nullable (e.g. *string) to avoid scan errors","articleBody":"#### 📌 Problem\n\nWhen using `sqlc v1.30.0` with a PostgreSQL function that returns a nullable scalar type like `TEXT`, `sqlc` generates non-pointer types (e.g. `string`), which causes runtime errors when the function returns `NULL`.\n\n---\n\n#### 💥 Example\n\nGiven the function:\n\n```sql\nCREATE FUNCTION mark_email_as_verified(p_user_id UUID)\nRETURNS TEXT AS $$\nBEGIN\n RETURN NULL; -- success\nEND;\n$$ LANGUAGE plpgsql;\n```\n\nAnd the query:\n\n```sql\n-- name: MarkEmailAsVerified :one\nSELECT mark_email_as_verified($1) AS result;\n```\n\n`sqlc` generates:\n\n```go\nfunc (q *Queries) MarkEmailAsVerified(ctx context.Context, pUserID uuid.UUID) (string, error)\n```\n\nIf the function returns `NULL`, this causes:\n\n```\ncan't scan into dest[0] (col: result): cannot scan NULL into *string\n```\n\nOr similar runtime panic from Go's database/sql or pgx drivers.\n\n---\n\n#### ✅ Expected Behavior\n\nThe generated Go function should be:\n\n```go\nfunc (q *Queries) MarkEmailAsVerified(ctx context.Context, pUserID uuid.UUID) (*string, error)\n```\n\nSince PostgreSQL functions returning `TEXT`, `UUID`, `INT`, etc. **can return NULL**, sqlc should **always generate pointer types** for these cases.\n\n---\n\n#### ✅ Workaround\n\nSet the following in `sqlc.yaml` (v1.30.0 supports this):\n\n```yaml\nversion: \"2\"\nsql:\n - engine: \"postgresql\"\n schema: \"schema.sql\"\n queries: \"queries.sql\"\n emit_pointers_for_null_types: true\n```\n\nThis ensures nullable scalar types like `TEXT` are correctly mapped to Go pointers (e.g. `*string`), avoiding scan errors.\n\n---\n\n#### 💡 Suggested Improvements\n\n1. **Default Behavior**:\n\n * Treat **function return values as nullable** unless explicitly known to be non-null.\n * Generate Go pointer types (`*string`, `*uuid.UUID`, etc.) for any scalar-returning function.\n\n2. **Optional per-query override**, e.g.:\n\n ```sql\n -- name: MarkEmailAsVerified :one\n -- nullable: true\n SELECT mark_email_as_verified($1);\n ```\n\n3. **Better error message**:\n\n * When the generated code causes a scan error due to NULL, sqlc should suggest enabling `emit_pointers_for_null_types`.\n\n---\n\n#### 📦 Version Info\n\n* `sqlc` version: `v1.30.0`\n* SQL engine: PostgreSQL\n* Go driver: pgx (via `pgx/v5`)\n\n---\n\n#### ✅ Summary\n\nThis bug leads to runtime error that are **non-obvious and difficult to trace**, especially for newer users.\n\nSince PostgreSQL functions can always return `NULL`, sqlc should default to generating nullable Go types (`*T`) or offer a safer default behavior to avoid silent breakage.\n\n---\n","author":{"url":"https://github.com/aaffriya","@type":"Person","name":"aaffriya"},"datePublished":"2025-10-02T04:56:39Z","url":"https://github.com/sqlc-dev/sqlc/issues/4121"}
| 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:d508494e-a079-627f-11c7-113b0db85f7c |
| current-catalog-service-hash | 81bb79d38c15960b92d99bca9288a9108c7a47b18f2423d0f6438c5b7bcd2114 |
| request-id | B5F4:842EC:21AC7D7:2CE23A6:6B018B5C |
| html-safe-nonce | c81f798983363dd311fba176cfb167000e9e81a7cf0078426093efd5721d3928 |
| visitor-payload | eyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiJCNUY0Ojg0MkVDOjIxQUM3RDc6MkNFMjNBNjo2QjAxOEI1QyIsInZpc2l0b3JfaWQiOiI5MTEzNDYyNTE5ODc1MzQxMTQ4IiwicmVnaW9uX2VkZ2UiOiJpYWQiLCJyZWdpb25fcmVuZGVyIjoiaWFkIn0= |
| visitor-hmac | ec720366da21a47a50019ff8a2893336ab08198dceb93dec3237a27b46bf8337 |
| hovercard-subject-tag | issue:3476018539 |
| 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/4121/issue_layout |
| twitter:image | https://opengraph.githubassets.com/33ff5fe15502881f3ff9f8087c2edd10fe1a37662d9c491d2b3175f013e4febb/sqlc-dev/sqlc/issues/4121 |
| twitter:card | summary_large_image |
| og:image | https://opengraph.githubassets.com/33ff5fe15502881f3ff9f8087c2edd10fe1a37662d9c491d2b3175f013e4febb/sqlc-dev/sqlc/issues/4121 |
| og:image:alt | 📌 Problem When using sqlc v1.30.0 with a PostgreSQL function that returns a nullable scalar type like TEXT, sqlc generates non-pointer types (e.g. string), which causes runtime errors when the func... |
| og:image:width | 1200 |
| og:image:height | 600 |
| og:site_name | GitHub |
| og:type | object |
| og:author:username | aaffriya |
| 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