CODEWITHSUNDEEP
sqlpostgresqlwhere-clausenullable
mark
mark

Reputation: 62836

Negating SQL WHERE condition with nullable fields

I have a filter where user can select operations like is, contains, etc...

Now I have added the negative variants - is not, does not contain, etc.. I do not want to rewrite the WHERE conditions from scratch, so I prepend NOT to the already existing ones:

However, there is a problem. If bubu is a nullable field and it actually has NULL in it, then the negative WHERE condition does not pick it, although from the human perspective (as opposed to SQL) the NULL value should satisfy the bubu is not xoxo filter.

I solve this problem by modifying the original positive WHERE condition like this:

Then, the negation yields:

And this time the NULL values are picked up correctly. The same problem is with the contains filter.

Is there a more elegant solution to resolve this inconsistency between how humans treat NULL and how SQL does it?

I am using PostgreSQL 9.2 and I do not mind having a solution specific to this database.

P.S.

Please, note that I want the negative expression to be of the form NOT positive.

Upvotes: 1

Views: 359

Answers (2)

mu is too short
mu is too short

Reputation: 434785

I think you should be able to get away with using COALESCE to convert your NULLs to empty strings:

-- These skip skips NULLs
lower(coalesce(bubu, '')) = 'xoxo'
coalesce(bubu, '') ilike '%xo%'

-- These will find NULLs
not lower(coalesce(bubu, '')) = 'xoxo'
not coalesce(bubu, '') ilike '%xo%'

Of course, this sort of trickery will run into problems if you're searching for empty strings, in such cases you'll need a context-sensitive sentinel value so that you can intelligently choose something that cannot possibly match your search term.

Demo: http://sqlfiddle.com/#!12/8bbd2/3

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125424

For the = operator you can use the is [not] distinct from construct

WHERE (lower(bubu) is not distinct from 'xoxo')

http://www.postgresql.org/docs/9.2/static/functions-comparison.html

Upvotes: 0

Related Questions