CODEWITHSUNDEEP
postgresqlpsycopg2
Jonathan Vanasco
Jonathan Vanasco

Reputation: 15680

select an item with a null comparison

I'm not using a full on DB Abstraction library, and am using raw sql templates in psycopg2 that look like this :

 SELECT id FROM table WHERE message = %(message)s ;

The ideal query to retrieve my intended results looks something like this :

 SELECT id FROM table WHERE message = 'a3cbb207' ;
 SELECT id FROM table WHERE message IS NULL ;

Unfortunately... the obvious problem is that my NULL comparisons come out looking like this:

 SELECT id FROM table WHERE message = NULL ;

... which is not the correct comparison - and doesn't give me the intended result set.

My actual queries are much more complex than the illustration above - so I can't change them easily. ( which would be the correct solution , i agree. i'm looking for an emergency fix right now )

Does anyone know of a workaround , so I can keep the same singular templates going until a proper fix is in place ? I was trying to get coalesce and/or cast to work , but I struck out with my attempts.

Upvotes: 1

Views: 242

Answers (3)

Craig Ringer
Craig Ringer

Reputation: 324741

What you want is IS NOT DISTINCT FROM.

SELECT id FROM table WHERE message IS NOT DISTINCT FROM 'the text';

SELECT id FROM table WHERE message IS NOT DISTINCT FROM NULL;

NULL IS NOT DISTINCT FROM NULL is true, not NULL, so it's like = but with different NULL comparison semantics. Great in trigger functions.

AFAIK can't use IS DISTINCT FROM for index lookups though, so be careful there. It can be better to use separate tests for null and value.

Upvotes: 11

Mike Christensen
Mike Christensen

Reputation: 91666

Unfortunately, NULL does not actually equal anything (not even another NULL) as the value of NULL is intended to represent an unknown. Your best bet is to change your templates to handle this correctly.

If it's possible that you can pass in separate values for the left and right operand in your template, one way to still use an equal sign would be:

SELECT id FROM table WHERE true = (message is null);

Upvotes: 1

Diego
Diego

Reputation: 7562

You can try writing your query clause as follows:

WHERE message = %(message)s OR ((%message)s IS NULL AND message IS NULL))

It's a bit rough, but it means "select the message that match my parameter, or all the messages that are null if my parameter is null". It should do the trick.

Upvotes: 1

Related Questions