Wrong param type when using COALESCE

[said-saifi]

Summary

COALESCE works perfectly when your column is already nullable (no NOT NULL), however if your column does not accept null values then if you use COALESCE your argument in GO will be just be a string for example instead of sql.NullString

Version

v1.6.0

How to reproduce

-- Example queries for sqlc
CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text NOT NULL,
  bio  text
);

-- name: UpdateAuthor :exec
UPDATE authors
SET name=COALESCE($2, name) WHERE id=$1;

Or click here

Expected Results

type UpdateAuthorParams struct {
	ID   int64
	Name sql.NullString
}

Actual Results

type UpdateAuthorParams struct {
	ID   int64
	Name string
}

So as you can see Name field in UpdateAuthorParams is just of type string, so using COALESCE with that will be useless.

Possible Alternatives

If you don't have to make your column NOT NULL in the database then this works as shown here

[idirall22]

@said-saifi If you want to make the column NOT NULL and still get the possibility to set the update as optional you can use this Postgres trick

• For a string: "name"=COALESCE(NULLIF(@name::varchar, ''), "name")
• For a integer: "price"=COALESCE(NULLIF(@price::int, 0), "price"),
• For a date: "birthday"= COALESCE(NULLIF(@birthday::date, '0001-01-01')::date, "birthday"),

[said-saifi]

@idirall22 thanks, this alternative works. I tried using NULLIF before, however without named variables which does not work in this package as you mentioned in another ticket.

[jamesleeht]

Seems like this is still an issue. The SELECT COALESCE was fixed by @mpyw but UPDATE COALESCE is still generating non-null types

[kyleconroy]

A workaround that we haven't discussed is using sqlc.narg.

-- name: UpdateAuthor :exec
UPDATE authors
SET name=COALESCE(sqlc.narg(name), name) WHERE id=$1;

PostgreSQL itself treats all query parameters as nullable. Using sqlc.arg and sqlc.narg gives you full control over parameter nullability.

https://play.sqlc.dev/p/e87f59905b718f4b79b1a30cb0ec78713d033932b6c00ae8cbb899fa663bc6e7

[irfanabdnst]

In my case, when I'm using sqlite engine, the generated query parameter for sqlc.narg('email') is ?1. Sqlite doesn't support parameter with ?<number>

-- name: UpdateUser :one
UPDATE
    tb_user
SET
    email       = COALESCE(sqlc.narg('email'), email), 
    name        = COALESCE(sqlc.narg('name'), name),
    role        = COALESCE(sqlc.narg('role'), role), 
    image       = COALESCE(sqlc.narg('image'), image),
    updated_at  = strftime('%s','now')
WHERE
    id = sqlc.arg('id')
RETURNING *;

-- generated query
UPDATE
    tb_user
SET
    email       = COALESCE(?1, email), 
    name        = COALESCE(?2, name),
    role        = COALESCE(?3, role), 
    image       = COALESCE(?4, image),
    updated_at  = strftime('%s','now')
WHERE
    id = ?5
RETURNING id, created_at, updated_at, email, name, password, role, image

[timsofteng]

The issue still exists. @mpyw do you think is possible to fix it for UPDATE queries as well as you did for SELECT?

[danuiachimovschi]

Hey! The issue still exists

COALESCE(pdc.Code, dc.Code) as deliveryCountryCode

type MyStruct struct {
    Deliverycountrycode string
}

[krhubert]

The coalesce doesn't work when column uses custom go type, like in this example:

          - column: "user.timezone"
            go_type: "github.com/org/myrepo/postgres/postgrestype.Timezone"

          - column: "user.timezone"
            go_type:
              import: "github.com/org/myrepo/postgres/postgrestype"
              type: "Timezone"
              pointer: true
            nullable: true

create table if not exists "user" (
    id uuid primary key,
    email text not null,
    first_name text not null default '',
    last_name text not null default '',
    timezone text not null default 'UTC'
);


-- name: UserUpdate :one
update "user" set
    email = lower(coalesce(sqlc.narg(email), email)::text)
  , first_name = coalesce(sqlc.narg(first_name), first_name)
  , last_name = coalesce(sqlc.narg(last_name), last_name)
  , timezone = coalesce(sqlc.narg(timezone), timezone)
where id = sqlc.arg(id)
returning *;

type UserUpdateParams struct {
  Email       sql.Null[string]
  FirstName   sql.Null[string]
  LastName    sql.Null[string]
  Timezone    postgrestype.Timezone // should be *postgrestype.Timezone
  Id          uuid.UUID
}