postgresql invalid input syntax for type double precision

Question

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

Never store timestamp values in varchar columns. You should fix that broken data model before you proceed — user330315
– user330315, Commented Feb 23, 2022 at 6:23
what data type do you recomend? i need it to be compatible with the case statement — Jose Armas
– Jose Armas, Commented Feb 24, 2022 at 23:31

Jose Armas · Accepted Answer · 2022-02-23 03:14:08Z

1

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.

answered Feb 23, 2022 at 3:14

Jose Armas

331 silver badge9 bronze badges

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

Collectives™ on Stack Overflow

postgresql invalid input syntax for type double precision

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