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