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 — a_horse_with_no_name
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

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2020-03-09T21:06:32

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

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

1 Answers