Description
Version
1.28.0
What happened?
Describe the bug
When using COALESCE in an UPDATE query, SQLC generates Go parameters with non-nullable types (e.g., int32, string) instead of nullable pointers (e.g., *int32, *string) or using pgtype. This prevents passing nil to skip updating specific fields, even though the query logic supports it.
Steps to Reproduce
- Table Schema:
CREATE TABLE IF NOT EXISTS lessons (
id BIGSERIAL PRIMARY KEY,
index INTEGER NOT NULL, -- Mandatory field [[9]]
name TEXT NOT NULL, -- Mandatory field
description TEXT NOT NULL DEFAULT '',
is_online BOOLEAN NOT NULL DEFAULT false,
date TIMESTAMP NOT NULL,
subject_id BIGINT REFERENCES subjects (id),
class_id BIGINT REFERENCES classes (id),
teacher_id BIGINT REFERENCES users (id),
schedule_number INTEGER NOT NULL
); - Query:
-- name: UpdateLesson :exec
UPDATE lessons
SET
index = COALESCE($1, index),
name = COALESCE($2, name),
description = COALESCE($3, description),
is_online = COALESCE($4, is_online),
date = COALESCE($5, date),
subject_id = COALESCE($6, subject_id),
class_id = COALESCE($7, class_id),
teacher_id = COALESCE($8, teacher_id),
schedule_number = COALESCE($9, schedule_number)
WHERE id = $10;- Generated Parameters:
type UpdateLessonParams struct {
Index int32 // Non-nullable [[1]][[9]]
Name string // Non-nullable
Description string
IsOnline bool
Date pgtype.Timestamp // Requires manual NULL handling
SubjectID pgtype.Int8
ClassID pgtype.Int8
TeacherID pgtype.Int8
ScheduleNumber int32
ID int64
}Expected Behavior
Parameters for fields wrapped in COALESCE should be nullable pointers (e.g., *int32, *string) to allow nil values for skipping updates
Actual Behavior
SQLC generates non-nullable types based on the table schema’s NOT NULL constraints, ignoring the query’s COALESCE logic
Workaround
Manually redefine the generated struct with nullable types or modify table to use nullable types.
Relevant log output
Database schema
CREATE TABLE IF NOT EXISTS lessons (
id BIGSERIAL PRIMARY KEY,
index INTEGER NOT NULL, -- Mandatory field [[9]]
name TEXT NOT NULL, -- Mandatory field
description TEXT NOT NULL DEFAULT '',
is_online BOOLEAN NOT NULL DEFAULT false,
date TIMESTAMP NOT NULL,
subject_id BIGINT REFERENCES subjects (id),
class_id BIGINT REFERENCES classes (id),
teacher_id BIGINT REFERENCES users (id),
schedule_number INTEGER NOT NULL
);SQL queries
-- name: UpdateLesson :exec
UPDATE lessons
SET
index = COALESCE($1, index),
name = COALESCE($2, name),
description = COALESCE($3, description),
is_online = COALESCE($4, is_online),
date = COALESCE($5, date),
subject_id = COALESCE($6, subject_id),
class_id = COALESCE($7, class_id),
teacher_id = COALESCE($8, teacher_id),
schedule_number = COALESCE($9, schedule_number)
WHERE id = $10;Configuration
version: "2"
sql:
- engine: "postgresql"
queries: "internal/models/db/query.sql"
schema: "internal/models/db/schema.sql"
gen:
go:
package: "db"
out: "internal/models/db"
sql_package: "pgx/v5"Playground URL
https://play.sqlc.dev/p/d01115f99caeeadb87e2229132ebea97c8a8c36278e2339b136bbd5a30fcdfd2
What operating system are you using?
Linux
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go