sqlc: Optional WHERE parameters via `$1 IS NULL OR`

CREATE TABLE users ( name TEXT NOT NULL );

-- name: ListUsers :many
SELECT *
FROM users
WHERE
  ($1 IS NULL OR name = $1);

Sometimes I do something like this to optionally query by a field. Since there’s no type associated with $1 IS NULL sqlc fails: nodes.ResTarget has nil name.

I can fix this by re-ordering the WHERE clause:

SELECT *
FROM users
WHERE
  (name = $1 OR $1 IS NULL);

But now the generated type is string instead of sql.NullString

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 22
Comments: 16 (5 by maintainers)

Most upvoted comments

1.14.0 includes sqlc.narg which generates nullable arguments.

CREATE TABLE users ( name TEXT NOT NULL );

-- name: ListUsers :many
SELECT *
FROM users
WHERE
  (name = sqlc.narg('name') OR sqlc.narg('name') IS NULL);

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

kyleconroy on Jun 12, 2022

@louis77 @imraan-go since I wanted to stick with sqlc for now, I’ve taken to adding some bool inputs to do what I want. It’s not super convenient but not terrible either:

-- name: GetThings :many
SELECT *
FROM things
WHERE
  color = sqlc.arg('Color') AND
  (NOT sqlc.arg('HasCreatedFrom')::bool OR (created >= sqlc.arg('CreatedFrom'))) AND
  (NOT sqlc.arg('HasCreatedTo')::bool OR (created < sqlc.arg('CreatedTo')))
LIMIT sqlc.arg('Limit')
OFFSET sqlc.arg('Offset');

This is icky in my personal opinion but it would certainly be pretty easy to rip and replace (for my use case) if this gets fixed. 300 queries is a lot, my sympathies.

danielbprice on Mar 7, 2022

Hi! Related to this issue, how can i generate a pointer type for parameters?

-- schema.sql

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    full_name TEXT NOT NULL
);

-- queries.sql

-- name: UserList :many
SELECT * FROM users
WHERE 
(email = $1 or $1 IS NULL) AND
(full_name = $2 or $2 IS NULL) ;

Currently, SQLC generated:

type UserListRequest struct {
  Email     string  `json:"email"`
  FullName  string  `json:"full_name"`
}

But, Because we want Email, FullName are options (when user do not provide -> will skip it in SQL statement), so, we need:

type UserListRequest struct {
  Email     *string  `json:"email"`
  FullName  *string  `json:"full_name"`
}

ryda20 on Sep 10, 2020