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.

Please show both sample data and the expected result as tables in your question. — Jonas Metzler, Jul 24 '23 at 12:13
What @Stu said will do the trick. What I usually do in similar cases is: COALESCE(NULLIF($3, ''), industry_id) = industry_id just in case a NULL value sneaks in, instead of an empty string. — D.Tabakov, Jul 24 '23 at 13:25
@D.Tabakov That worked perfectly as I wanted to do and it's so simple. Thank you!. — Islom, Jul 25 '23 at 04:14

score 0 · Answer 1 · answered Jul 24 '23 at 12:34

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: %v\n", qs.String())

answered Jul 24 '23 at 12:34

Shahriar Ahmed

502
4
11

*note:* this example is vulnerable to sql injection – JBirdVegas Jul 24 '23 at 16:29
But parameters will be parsed from somewhere with there associated type then it will be put in map[string]interface{}. It that is the case then no risk. – Shahriar Ahmed Jul 24 '23 at 16:45
if my city code was "NYC; DROP TABLE records;" then I would drop your table and the next line would error. Assuming all the inputs are properly sanitized then yes the string template will work – JBirdVegas Jul 24 '23 at 16:56
Thanks for your explanation. now understood. – Shahriar Ahmed Jul 25 '23 at 03:49

score 0 · Accepted Answer · answered Jul 25 '23 at 04:16

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;

answered Jul 25 '23 at 04:16

Islom

69
8

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

2 Answers2