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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
