PSQL - invalid input syntax for type double precision: “null”;

Question

I'm writing a query to pull some data and running into this invalid input syntax for type double precision: "null" error and am not entirely sure the meaning behind it and/or haven't figured out a solution. The column I'm running the case statment on does have null values in it or it has the appropriate value that i need.

account id col could have strings or it has null values

I've tried:

case
    when length(account_id) > 1
      then account_id
    else
      'null'
    end as account_id,

and

case
    when account_id is not null
      then account_id
    else
      'null'
    end as account_id,

both resulting in Invalid operation: invalid input syntax for type double precision: "null";

When i just put this in a column and use the length(account_id) it will show the length or just display null - why wouldn't this work in a case statement?

Thanks!

the columns seems to be a numeric data type, and you are using 'null' as in a string, not as NULL — Lamak
What's the data type of account_id? It's complaining is double precision. — The Impaler

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-05-31T21:52:55

'null' is a string. null is . . . well null. Your account_id appears to be of type double precision. That is a strange choice for an account_id, but inappropriate data types are not relevant. What is relevant is that the case expression wants everything to be of the same type, with numbers getting precedence.

You may want one of these two things:

(case when length(account_id) > 1 then account_id end) as account_id,

The default is null when there is no else. You could add else null, but that is redundant.

Or:

(case when length(account_id) > 1 then account_id::varchar(255) else 'null' end) as account_id,

PSQL - invalid input syntax for type double precision: “null”;

1 Answers