Question 215285 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    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: %vn", qs.String())
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search