typecast to int in postgres

Question

I am trying to query a table which a json column

select * 
from subscription 
where extras->>'end_date' > 1592424632;

this errors out saying

ERROR: invalid input syntax for integer: "end_date"

I have tried type casting

select * 
from subscription 
where extras->>'end_date'::int4 > 1592424632;

ERROR: invalid input syntax for integer: "end_date"

and extras column sample input looks like

{"end_date": 1592425146, "capacity": 1, "start_date": 1584562746, "devices": "", "quantity": 10}

@a_horse_with_no_name Good catch! Learning every day :-). postgresql.org/docs/current/functions-json.html — Ben
– Ben, Commented May 29, 2020 at 7:20

user330315 · Accepted Answer · 2020-05-29 07:17:29Z

4

You need parentheses. The cast operator :: has a higher precedence than the -> operator. So extras->>'end_date'::int4 is parsed as extras->> ('end_date'::int4) which is the reason for the error.

select * 
from subscription 
where (extras->>'end_date')::int4 > 1592424632

answered May 29, 2020 at 7:17

user330315

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

Collectives™ on Stack Overflow

typecast to int in postgres

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