Go language single database query to address many parameters

I am building a Rest API, in the format of query/{occupation}/{location}

Now, in the .go file, I have

position := params["occupation"]
loc := params["location"]
rows, err := db.Query(`SELECT occupation, location 
                       FROM myTable WHERE occupation=$1 AND location=$2`,
                       position, loc);

This works great if we call my API with both occupation and location exist.
I want it to be more flexible so I can ignore some parameters, for example, I want to call abc.com/query/dentist/NULL.

Then I could have this code:

if loc == nil && position == nil {
    rows, err = db.Query(`SELECT occupation, location 
                       FROM myTable);
} else if loc == nil {
    rows, err = db.Query(`SELECT occupation, location 
                       FROM myTable WHERE occupation=$1`, position);
} else if position = nil {
    rows, err = db.Query(`SELECT occupation, location 
                       FROM myTable WHERE location=$1`, loc);
} else {
    rows, err := db.Query(`SELECT occupation, location 
                       FROM myTable WHERE occupation=$1 AND location=$2`,
                       position, loc);
}

But this is not sustainable since my RestAPI may support 10 parameters and the if else clauses would grow to 2^10.

I am wonder just using one query to address all cases.

This maybe a simple SQL question, I just cannot figure it out (I'm using postgres).

It's really a basic variable checking, you don't need to do all 3 queries, for example :

var (
	rows     *sql.Rows
	err      error
	position = params["occupation"]
	loc      = params["location"]
)
switch {
case loc != "" && position != "":
	rows, err = db.Query(`SELECT occupation, location FROM myTable WHERE occupation=$1 AND location=$2'`, position, loc)
case loc != "":
	rows, err = db.Query(`SELECT occupation, location FROM myTable WHERE location=$1`, loc)
case position != "":
	rows, err = db.Query(`SELECT occupation, location FROM myTable WHERE occupation=$1`, position)
default:
	rows, err = db.Query(`SELECT occupation, location FROM myTable`)
}
if err != nil {
	panic(err)
}

Maybe you should use some ORM for better design.

This should help: https://github.com/jinzhu/gorm#query-with-where-struct--map

Or you can compile the where condition by concatenating SQL and adding bind parameters to query. But watch out for SQL injection.

To ignore a parameter pass it as null and change the query to :

select occupation, location
from mytable
where
    (occupation = $1 or $1 is null)
    and
    (location = $2 or $2 is null)

This will scale linearly with the number of parameters.