postgresql: getting error invalid entry syntax for double precision type: "Null"

Question

Having a table with 3 columns as Following:

Original data look like:

I am trying to convert the table into hourly data by suming Energy values. I used this command but got an error => invalid entry syntax for double precision type: "Null":

SELECT lclid, date_trunc('hour', tstp) AS HOUR, COALESCE(SUM(CAST(energy as double precision)) 
FROM halfhourly 
WHERE energy IS NOT NULL 
GROUP BY lclid, HOUR;

I have been looking for a while but cannot find the solution. Please help!

You shouldn't be storing numbers in a varchar column to begin with — user330315
– user330315, Commented Mar 9, 2020 at 20:13
I tried to convert the varchar into numeric but didn't work. Got the same error as the above one from this topic. I did: ALTER TABLE halfhourly ALTER COLUMN energy TYPE numeric USING energy::numeric; — k_bm
– k_bm, Commented Mar 9, 2020 at 20:59

user330315 · Accepted Answer · 2020-03-09 21:06:32Z

2

Your column contains the string literal 'Null'. You need to convert that to a proper null value. This can e.g. be done using nullif()

 CAST(nullif(lower(energy),'null') as double precision)

You can use the same expression to properly convert this column to a double precision type:

alter table halfhourly
   alter column energy type double precision 
   using CAST(nullif(lower(energy),'null') as double precision);

Note that this only deals with the error you have shown. If you have more invalid values in that column, you will have to deal with each of them in a similar manner

answered Mar 9, 2020 at 21:06

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql: getting error invalid entry syntax for double precision type: "Null"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related