How to add to Postgresql where clause when number of valid variables is initially unknown?

Question

Hello dev community :)

Stack: nodejs, Postgresql, npm: pg

How can I add to my query 'and' statement in 'where' clause for variables which can be valid or can be undefined?

Example

`select col1, col2 from table where col1='${test1}' ` <-- add here 'and col2='${test2}'

how to add "and col2=${test2}" dynamically when test 2 is not undefined and leave things as is when it is undefined. How to do the same for multiple entries like 'and col3=test3 and col4=test4....and colN=testN'?

Currently Postgresql returns error if I add 'and' as string in concatenation. What is the proper way to concatenate it?

Thank you all ;)

Answer 1

Because the query contains order by, limit,offset direct concatenation with parametrized query wouldn't work, so I found another solution concatenating outside of query. Yes it has some speed issues while it has to scan more, still it works.

let var1 = 123
let var2 = undefined
let query = ('select col1,col2,col3 from foo order by col1 limit 100 offset 25')

if (var1 && var2){
   query='select * from ('+query+')t where t.col1=var1 and t.col2=var2'
} else {
   query='select * from ('+query+')t'
}

}