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

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"`
}

You can force type by casting it with something like $1::varchar but I did not find a way to make the input value optional! Did you solved the issue?

Seems like a pressing issue, optional parameters is something really common.

Changing the model (schema) to "nullable" with a default is not a viable option (forcing sql.NullXXX) and I'm not sure how overrides can help, since the input is not a part of the schema, we need a way to create nullable inputs ⭕

I would be happy to submit a PR but I would appritiate some pointers to where to start <3

I'm going to split this into two separate issues. The original query in this issue is still causing sqlc generate to fail with the following error message:

unsupported reference type: <nil>

The other part of this issue deals with marking parameters as nullable. I've created a new issue (#1155) to track this where we can discuss possible solutions.

This has become a real bummer for me. I am trying to port some boring "take REST request and turn it into SQL query" code to Go and I would like to use sqlc; the REST API has optional inputs which become clauses on the WHERE clause, just like the above example. I feel really stuck at this point, and I think I will have to abandon using sqlc in this project as a result. I'm back to writing boilerplate again. Maybe someone else has a clever workaround I've missed?

@danielbprice

@danielbprice I'm stuck at exactly the same issue. I found no solution to this and it looks like this is because sqlc completely relies on a 1:1 mapping between the schema definition and the query, which is, more often than not, not reality. I have about 300 different queries and more than half of them I'm unable to map it with sqlc. So it's time to say hello to good old sqlboiler.

@danielbprice

@danielbprice I'm stuck at exactly the same issue. I found no solution to this and it looks like this is because sqlc completely relies on a 1:1 mapping between the schema definition and the query, which is, more often than not, not reality. I have about 300 different queries and more than half of them I'm unable to map it with sqlc. So it's time to say hello to good old sqlboiler.

Yep. I had to abandon using this. Just use some orm like gorm or bun.

@louis77

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

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

Hi guys, how about optional where in arg ? any example ?

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

hey kyle, how can I use this with COALESCE? (e.g. ... LIMIT COALESCE(sqlc.narg('limit_val'), 10)) currently this syntax returns unsupported reference type: <nil>
I've also tried something like:

LIMIT CASE
        WHEN sqlc.narg('limit_val') IS NOT NULL THEN sqlc.narg('limit_val')
        ELSE 10
    END;

@omid9h

@omid9h I have clauses like this all over my sqlc code. Is this what you mean?

https://play.sqlc.dev/p/9f0657e82b95ad3b78df722c96912b3c3ff3ed8ae6fc82097b5303f4564379f6

CREATE TABLE users ( name TEXT NOT NULL );

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

(postgres, sqlc 1.19; sqlite also seemed to work; mysql gave me another error, but I'm not sure what the right syntax is there)

COALESCE

Hmm, that is weird. my code is like yours but I get the error.

-- name: ListSample :many
SELECT id,
    title,
    created_at,
    updated_at
FROM sample
WHERE (
        title ILIKE sqlc.narg('title')
        OR sqlc.narg('title') IS NULL
    )
    AND (
        created_at >= sqlc.narg('start_created_at')
        OR sqlc.narg('start_created_at') IS NULL
    )
    AND (
        created_at >= sqlc.narg('end_created_at')
        OR sqlc.narg('end_created_at') IS NULL
    )
ORDER BY created_at DESC OFFSET sqlc.narg('offset_val')
LIMIT COALESCE(sqlc.narg('limit_val'), 100);

but both of these lines work:

LIMIT @limit_val;

LIMIT sqlc.narg('limit_val')@limit_val;

sqlc.yaml:

version: "2"
sql:
  - engine: "postgresql"
    queries: "db/queries/"
    schema: "db/migrations/"
    gen:
      go:
        package: "db"
        sql_package: "pgx/v5"
        out: "db"

sqlc version : v1.19.0

@omid9h

@omid9h, I tried to replicate your setup on the sqlc playground, and I had no luck getting it fire the error you mentioned.
https://play.sqlc.dev/p/4a9082b92e8aa9a5f1801a14b9282130a9c2ae1d5a5f47da40b7617c03fd65f3

Can you review that and see if I made a mistake? I had to guess the schema, translate your config to JSON, and tweak it for the filenames of the playground, but your query is the same. I assume what you are saying is that the code doesn't compile, right? I'm sorry I can't help you more! Maybe someone else will know.

@danielbprice

Hi @danielbprice , I have the exact same issue than @omid9h.

When trying LIMIT COALESCE(sqlc.narg('limit'), 50), I have the error unsupported reference type: <nil>.
(Postgres 14, sqlc 1.19.1, MacOS)

@omid9h, I tried to replicate your setup on the sqlc playground, and I had no luck getting it fire the error you mentioned. https://play.sqlc.dev/p/4a9082b92e8aa9a5f1801a14b9282130a9c2ae1d5a5f47da40b7617c03fd65f3

In your example, there is something very weird. As you see, the generated ListSampleParam looks like

type ListSampleParams struct {
	Title          pgtype.Text
	StartCreatedAt pgtype.Timestamptz
	EndCreatedAt   pgtype.Timestamptz
	OffsetVal      pgtype.Int4
}

It's missing LimitVal pgtype.Int4. I don't know if it's related on play.sqlc.dev environment or not.

@omid9h

After some tests, I have been able to make it works with LIMIT.

All you have to do is to cast the argument into int. So, in your case @omid9h:

LIMIT COALESCE(sqlc.narg('limit_val')::int, 100);

Also works on the example of @danielbprice: https://play.sqlc.dev/p/1bd81d42563767cc41714bd39570eba50ca17adc81bdee07ebab0d9ac8f7566f

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

now generates go name redeclared in this block, go does not compile

Navigation Menu

Repository navigation

Optional WHERE parameters via `$1 IS NULL OR` #200

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Optional WHERE parameters via $1 IS NULL OR #200

Description

Activity

ddnm102 commented on Sep 10, 2020

ludusrusso commented on May 8, 2021

mrsufgi commented on Jun 28, 2021

kyleconroy commented on Aug 28, 2021

danielbprice commented on Feb 15, 2022

louis77 commented on Feb 28, 2022

imraan-go commented on Mar 4, 2022

danielbprice commented on Mar 7, 2022

kyleconroy commented on Jun 12, 2022

akhmadnurmuhammad commented on Jul 21, 2022

omid9h commented on Jul 9, 2023

danielbprice commented on Jul 9, 2023

omid9h commented on Jul 9, 2023

danielbprice commented on Jul 11, 2023

francisco-bodyguard commented on Jul 26, 2023

francisco-bodyguard commented on Jul 27, 2023

sergeishilkocardmarket commented on Nov 21, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

Optional WHERE parameters via `$1 IS NULL OR` #200