Re: Problem using NULLIF in a CASE expression

Bruno BAGUETTE wrote:
>
> I wrote another (and quite shorter!) SQL query to resume the problem :
>
> SELECT CASE NULLIF(btrim(' A string', ' '), '')
> WHEN NOT NULL
> THEN NULL
> ELSE 6
> END AS type_id;
>
> ERROR: operator does not exist: text = boolean

> Why this query does not accept the NULLIF ?

It's not the NULLIF, it's the "WHEN NOT NULL". If you reverse the logic
of the case it works:
SELECT CASE (nullif(btrim(' ',' '), ''))
WHEN NULL THEN 'a'::text
ELSE 'b'::text
END AS test;

I think it's because (NOT NULL) is typed as a boolean (because that's
what the NOT operator returns) and you're comparing it to the text
output of your NULLIF(...). Don't forget the WHEN clause is supposed to
have a value attached (although of course NULL complicates matters).

I'd say the better solution is to clean up the data though. Add a BEFORE
INSERT/UPDATE trigger that corrects the bad applications and then you
won't have to jump through these hoops. If the varchar should be null or
have non-space content then enforce it!
--
Richard Huxton
Archonet Ltd