postgres check if null then cast to numeric

Question

I am trying check if a value is null if so the select null else cast to numeric, but it throws an error. This is actually part of an insert statement

INSERT into someTable(name,created,power) 
SELECT 'xyz',now(),
case when :power ='null' then NULL else cast(:power as numeric) end from abc

error that I get is

Error: ERROR: invalid input syntax for type numeric: "null"

:power is a variable that can be given any value using java code. If I give a value of null it give an error.

In code I get the following error from the java stack trace

org.postgresql.util.PSQLException: ERROR: cannot cast type bytea to numeric

what is `:power` supposed to be? That is not valid PL/pgSQL syntax — , Feb 09 '12 at 20:41

score 9 · Accepted Answer · edited May 23 '17 at 12:06

Error:

SELECT CASE WHEN 'null' = 'null' THEN NULL ELSE cast('null' AS numeric) END

No error:

DO $$
DECLARE
    power text := 'null';
BEGIN
    PERFORM CASE WHEN power = 'null' THEN NULL ELSE cast(power AS numeric) END;
END;
$$

Explanation:

If you build a query string, the expression cast('null' AS numeric) or simply 'null'::numeric always raises an exception, even in an ELSE block that is never executed, because it is invalid input syntax and the exception is raised during the syntax check (like the error message implies), not during execution.

A CASE statement like you display only makes sense with a parameter or variable not with literals. The second instance of the literal has no connection to the first instance whatsoever after the query string has been assembled.

For dynamic SQL like that, you need to check the value before you build the query string. Or you use a function or prepared statement and pass the value as parameter. That would work, too.

More advice after comment:

In your particular case you could check the value in the app and build a query string like this:

INSERT INTO tbl(name, abc_id, created, power) 
SELECT 'xyz'
     , abc_id
     , now()
     , <insert_value_of_power_or_NULL_here> -- automatically converted to numeric
FROM   abc

You may be interested in a different approach to INSERT data from a file conditionally.
Use COPY for files local to the server or psql's meta-command \copy for files local to the client.

I removed some stuff of the sql that was not required. In the insert, I am selecting the id of the table abc so that is why I am using table abc. I am reading rows from a file and inserting the values into a table. I do check if the value in file is empty or null then set it to java null. And I am using parameters to set the value to null, but when it executes it screws up. — user373201, Feb 10 '12 at 02:03
as you said, I think this is what is causing the exception cast('null' AS numeric), but don't know how else to avoid it. This is the piece of code that converts empty string into null and is done before the sql executes — user373201, Feb 10 '12 at 02:07
if(rowValueList.get(i) != null && rowValueList.get(i).trim().equals("")){ rowValueList.set(i, null); } — user373201, Feb 10 '12 at 02:08

score 8 · Answer 2 · answered Feb 20 '13 at 10:45

8

if the field value is null, and you want in this case to map it to some value you can use coalesce(field_name, 'Some value') or coalesce(field_name, 123).
For full documentation see here.

answered Feb 20 '13 at 10:45

ramigg

1,287
1
15
16

score 1 · Answer 3 · answered Feb 09 '12 at 20:42

1

You have to check with the IS operator, and not with the equal when you dealing with NULL :

INSERT into someTable(name,created,power) 
SELECT 'xyz',now(),
case when :power IS null then NULL else cast(:power as numeric) end from abc

answered Feb 09 '12 at 20:42

aleroot

71,077
30
176
213

I tried that did not work as well. I am using squirrel sql to test it – user373201 Feb 09 '12 at 21:13
1

If we actually dealt with a `NULL` value, we'd need no `CASE` statement and could just cast to `numeric`. We obviously do not, and have to check if a **string** equals 'null'. The whole construct does not make sense for a completely different reason. – Erwin Brandstetter Feb 09 '12 at 22:37

score 0 · Answer 4 · edited Aug 10 '15 at 18:23

I was trying to do something similar in order to update/insert some records where a numeric value can be null or not. You can validate a variable before you send it to the function or inside the function depending the value passed (For me using a variable is better than use CASE WHEN THEN ELSE END CASE every time you need to validate the value)

So to work with the NULL values using a regular comparison operand in order to find a record to update can be done by turning transform_null_equals to ON

I hope this help someone

CREATE OR REPLACE FUNCTION update_insert_transaction(vcodaccount integer, vcodaccountaux text, 
  vdescription text, vcodgroup integer)
  RETURNS integer AS $$
DECLARE
  n integer = 0;
  vsql text = 'NULL'; 
BEGIN 
    IF vcodaccountaux <> '' THEN
        vsql = vcodaccountaux;
    END IF; 
        SET LOCAL transform_null_equals TO ON;
        EXECUTE 'UPDATE account_import_conf SET (codaccount, codaccountaux, description, codgroup) = 
            ('||vcodaccount||','||vsql||',trim('||quote_literal(vdescription)||'),'||vcodgroup||') 
            WHERE codaccount='||vcodaccount||' AND codaccountaux = '||vsql||' RETURNING * ';
    GET DIAGNOSTICS n = ROW_COUNT;
    IF n = 0 THEN
    EXECUTE 'INSERT INTO account_import_conf (codaccount, codaccountaux, description, codgroup)         
        SELECT '||vcodaccount||','||vsql||' ,trim('||quote_literal(vdescription)||'),'||vcodgroup||';';
    END IF;
RETURN n;
END;$$
  LANGUAGE plpgsql;

maniek · Answer 5 · 2012-02-10T10:22:34.350

0

INSERT into someTable(name,created,power) SELECT 'xyz',now(),
   case :power when 'null' then NULL else :power end::numeric from abc

edited Feb 10 '12 at 10:22

answered Feb 09 '12 at 21:56

maniek

7,087
2
20
43

postgres check if null then cast to numeric

5 Answers5

Error:

No error:

Explanation:

More advice after comment: