Is it possible to omit field in where clause in postgresql?

Question

In SQL I have following code:

-- name: FilterRecords :many
SELECT *
FROM records
WHERE industry_id = $3 and region_code = $4 and city_code = $5
OFFSET $1 LIMIT $2;

What I'm trying to achieve is to exclude industry_id from where clause if provided value for it is blank string. Because when I'm making query it's counting blank strings, but I want to omit it if it's blank and select only records where they match region_code and city_code.

Answer 1

This is the one made for me and worked correctly.

-- name: FilterApz :many
SELECT *
FROM apz
WHERE industry_id LIKE COALESCE(NULLIF($3, ''), '%%') and region_code = $4 and city_code = $5
OFFSET $1 LIMIT $2;

Answer 2

You can do this by golangs default text/template.

variables := map[string]interface{} {
    "industry_id": ...,
    "region_id": ...,
    "city_code": ...,
    "offset": ...,
    "limit": ...,
}

qs := new(strings.Builder)

template.Must(template.New("qt").Parse(`
SELECT *
FROM records where
    {{ if .industry_id-}} industry_id= {{.industry_id}} and {{- end }}
    region_code = {{.region_code}} and city_code = {{.city_code}}
OFFSET {{.offset}} LIMIT {{.limit}}`)).
        Execute(qs, variables)

fmt.Printf("qs: %v\n", qs.String())