How can I check if a value is NULL in PostgreSQL while quoting it?

Question

I have the following SQL query:

SELECT choice, COUNT(*) AS c
FROM Vote, Person
WHERE Vote.pid = Person.pid AND (Person.city = '%s' OR %s IS NULL) <-----
GROUP BY choice
ORDER BY c DESC, choice

I've included the Person.city = '%s' OR %s IS NULL part so that, if the parameter is NULL, the query will include all cities. This query works fine when the parameter is NULL, but when I enter a non-NULL value, say Paris, I get this error:

Error: column "Paris" does not exist at character 104

And as far as I can tell, this error happens because %s isn't quoted. But when I do quote it, if %s is NULL, then '%s' IS NULL doesn't evaluate to true.

How can I check I check if %s is NULL when it's quoted?

Daniel... You're comparing the value you expect in the column as the column name. See my answer for the correct statement. — hd1, May 01 '14 at 21:59
What happens client-side to these `%s`? This query is simply run through `sprintf` with the raw values and then passed to `PQexec`? — Daniel Vérité, May 03 '14 at 13:44
Yes - it's a homework assignment and we can assume that the values are valid. — Daniel, May 03 '14 at 13:50

score 0 · Answer 1 · answered May 01 '14 at 21:54

0

Perhaps you could try:

SELECT choice, COUNT(*) AS c
FROM Vote join
     Person
     on Vote.pid = Person.pid 
WHERE (Person.city = '%s' OR '%s' = 'NULL') 
GROUP BY choice
ORDER BY c DESC, choice;

You won't be able to search explicitly for "NULL", but that might be a small price to pay.

Note I also changed the join syntax to use explicit join syntax.

answered May 01 '14 at 21:54

Gordon Linoff

1,242,037
58
646
786

Is this the only solution? It seems sort of hacky, and it doesn't address the core problem of not being able to check if a parameter is NULL. – Daniel May 01 '14 at 21:59
@Daniel . . . I think more typically `NULL` wouldn't be used. Instead, a blank string would be and `person.city = '%s' or '%s' = ''` reads fine. – Gordon Linoff May 01 '14 at 22:27

score 0 · Answer 2 · edited Oct 19 '20 at 06:01

0

if your variable is not NULL your query should be:

SELECT choice, COUNT(*) AS c
FROM Vote as V join
     Person as P
     on V.pid = P.pid 
WHERE P.city = '%s'
GROUP BY choice
ORDER BY c DESC, choice;

... and if %s is NULL:

SELECT choice, COUNT(*) AS c
FROM Vote as V join
     Person as P
     on V.pid = P.pid 
GROUP BY choice
ORDER BY c DESC, choice;

Definitely not the most terse way, but certainly the clearest.

I originally misunderstood the question and for that I apologise. Hope that helps...

edited Oct 19 '20 at 06:01

Community

1
1

answered May 01 '14 at 21:58

hd1

33,938
5
80
91

Your column is city not the value you're passing it. The value you're passing should never be null. – hd1 May 01 '14 at 22:00
This is part of a C method that accepts a `char*` city parameter, which can be NULL. `P.city` is never `NULL` and your query isn't equivalent to the query in my question. – Daniel May 01 '14 at 22:01
And as I said, the query does work when `%s` is NULL. It doesn't work when it's not NULL. – Daniel May 01 '14 at 22:02
You should change your query to test `P.city is not NULL` when the variable passed in is blank then. – hd1 May 01 '14 at 22:05
I don't understand your comment - are you saying I should write a separate query when the variable is NULL? – Daniel May 01 '14 at 22:07
In response to your edit: the solution of writing two separate queries is an obvious one, but my question is asking if there's a way to do it in a single query. Or in a more concise manner, I'm asking how you can check if a parameter is NULL. – Daniel May 01 '14 at 22:08
I'd check that in your client code. When you build the statement, you can have, something like, `SELECT choice, COUNT(*) AS c FROM Vote as V join Person as P on V.pid = P.pid (?param != null : "WHERE p.city = '%s'" : "") GROUP BY choice, ORDER BY c DESC, choice;` Do note, my C knowledge is about 15 years out of date. – hd1 May 01 '14 at 22:10

score 0 · Answer 3 · edited May 23 '17 at 12:29

You should probably use prepared statements instead. Detailed instructions for C in this related answer:
Passing C variables into SQL command

Then you pass values as values instead of trying string concatenation, which also opens you up to SQL injection.

Your query could look like this (simplified with explicit JOIN syntax and positional parameters in GROUP BY and ORDER BY) :

SELECT v.choice, count(*) AS c
FROM   vote v
JOIN   person p USING (pid)
WHERE (p.city = $1 OR $1 IS NULL)
GROUP  BY 1
ORDER  BY 2 DESC, 1;

The parentheses are not needed here - only if you add more conditions since AND binds before OR.

Or you create a PL/pgSQL function that handles the NULL case in a smarter way. I wrote a detailed related answer just yesterday:
How to remove conditions from WHERE clause if parameters are NULL

If referential integrity is guaranteed (enforced by a foreign key constraint) and every vote has a related person, simplify the function call in case of a NULL value:

SELECT v.choice, count(*) AS c
FROM   vote v
GROUP  BY 1
ORDER  BY 2 DESC, 1;

How can I check if a value is NULL in PostgreSQL while quoting it?

3 Answers3