This Question have 1 answers right now.

Postgres escape a single quote

Question!

I have the following postgres query:

SELECT SUM(Cost) 
FROM DB
WHERE ID NOT IN (<parameter>)

<parameter> is a dynamic text field where multiple ID's need to be inserted. If you type in

123, 456

as ID's, it results in:

SELECT SUM(Cost) 
FROM DB
WHERE ID NOT IN ('123,456')

Which doesn't run properly.

I can change the query, but I can't change the input field. If you type in

123','456

It results in:

SELECT SUM(Cost) 
FROM DB
WHERE ID NOT IN ('123'',''456')

When you change the query into:

SELECT SUM(Cost) 
FROM DB
WHERE ID NOT IN ('<parameter>')

And you type in

123,456 Then it results in:

SELECT SUM(Cost) 
FROM DB
WHERE ID NOT IN (''123'',''456'')

I've got it working for Mysql, but not for Postgresql. Any idea how to trick postgresql?



Answers

Try something like:

SELECT SUM(Cost) 
FROM DB
WHERE ID != ALL(('{'||'123,456'||'}')::numeric[])

It will form array string from your input values : {123,456}, cast it to an array and check ID against all elements of array.



Video about Postgres escape a single quote