Обсуждение: Problem using NULLIF in a CASE expression
Hello,
I have a problem using NULLIF inside a CASE expression.
I explain the goal of my query : I have a table where there is two
fields : one varchar field and one INT8 field.
When the varchar field is empty I have to display the INT8 field.
If the varchar field is not empty I have to display a NULL value (even
if the INT8 field contains something).
BUT : that table is used by several applications and theses applications
does not always store NULL in the varchar field. Some applications store
an empty string '', or stores <space> char(s).
SO, I have to do a btrim and to get a NULL value when I just have ''
after the btrim.
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
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
Why this query does not accept the NULLIF ?
What can I do to solve that problem ?
Thanks in advance !
Bruno BAGUETTE.
You're misusing the case construct, and try to compare a text result
(from the 'nullif') with a boolean result (from the 'not null'). You
probably should use the other form of case:
SELECT CASE WHEN NULLIF(btrim(' A string', ' '), ') IS NULL THEN NULL
ELSE 6 END AS type_id;
I guess you were looking for something like:
db=> SELECT CASE WHEN NULLIF(btrim(' A string', ' '), '') IS NULL THEN
NULL ELSE 6 END AS type_id;
type_id
---------
6
(1 row)
db=> SELECT CASE WHEN NULLIF(btrim(' ', ' '), '') IS NULL THEN NULL ELSE
6 END AS type_id;
type_id
---------
(1 row)
Cheers,
Csaba.
On Fri, 2005-09-09 at 13:45, Bruno BAGUETTE wrote:
> Hello,
>
> I have a problem using NULLIF inside a CASE expression.
>
> I explain the goal of my query : I have a table where there is two
> fields : one varchar field and one INT8 field.
> When the varchar field is empty I have to display the INT8 field.
> If the varchar field is not empty I have to display a NULL value (even
> if the INT8 field contains something).
>
> BUT : that table is used by several applications and theses applications
> does not always store NULL in the varchar field. Some applications store
> an empty string '', or stores <space> char(s).
> SO, I have to do a btrim and to get a NULL value when I just have ''
> after the btrim.
>
> 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
> HINT: No operator matches the given name and argument type(s). You may
> need to add explicit type casts.
>
> Why this query does not accept the NULLIF ?
> What can I do to solve that problem ?
>
> Thanks in advance !
>
> Bruno BAGUETTE.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
Try this:
SELECT CASE
WHEN btrim(' A string', ' ') = '' OR <field-name> IS NULL
THEN NULL
ELSE 6
END AS type_id;
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Bruno BAGUETTE
Sent: Friday, September 09, 2005 4:45 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Problem using NULLIF in a CASE expression
Hello,
I have a problem using NULLIF inside a CASE expression.
I explain the goal of my query : I have a table where there is two
fields : one varchar field and one INT8 field.
When the varchar field is empty I have to display the INT8 field.
If the varchar field is not empty I have to display a NULL value (even
if the INT8 field contains something).
BUT : that table is used by several applications and theses applications
does not always store NULL in the varchar field. Some applications store
an empty string '', or stores <space> char(s).
SO, I have to do a btrim and to get a NULL value when I just have ''
after the btrim.
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
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
Why this query does not accept the NULLIF ?
What can I do to solve that problem ?
Thanks in advance !
Bruno BAGUETTE.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
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