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

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!

1 Answer

'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,