sqlpostgresqlto-timestamp

postgresql invalid input syntax for type double precision

I have a table with the birth and death of several europe monarchs, the table doesn't have have an age column and i have to add it. There are also several null and 'None' values, in that case the age column should display unknow.

the following code gives me a invalid input syntax for type double precision error.

    SELECT CASE
WHEN date_part('year', age(TO_TIMESTAMP(death, 'YYYY-MM-DD'), TO_TIMESTAMP(birth, 'YYYY-MM-DD'))) = 'None' OR death ISNULL
    THEN 'Unknown'
ELSE
    date_part('year', age(TO_TIMESTAMP(death, 'YYYY-MM-DD'), TO_TIMESTAMP(birth, 'YYYY-MM-DD')))
END

AS age FROM monarchs

postgresql doesn't have a datediff function. Many thanks.

EDIT

The birth and death columns are varchars and have this format 0208-10-01T00:53:28+00:53

Solution

  • Resolved

    Used the following code

    CASE 
   WHEN death IS NULL OR birth IS NULL
      THEN 'Unknown'
      ELSE CAST(EXTRACT(YEAR FROM TO_TIMESTAMP(death, 'YYYY-MM-DD')) - EXTRACT(YEAR FROM TO_TIMESTAMP(birth, 'YYYY-MM-DD')) AS VARCHAR)
END

    'null' is a string. My query was doing a substraction and case statements needs to be of the same data type so I had to typecast it to varchar.