SQLC generates non-nullable parameters for nullable fields in UPDATE query despite COALESCE usage (pgx driver)

[CyberTea0X]

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

1. 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
   ); 

2. 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;

3. 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