divide operator error in PostgreSql: operator does not exist: unknown /

Question

I have a Trip table in PostgreSQL DB, there is a column called meta in the table.

A example of meta in one row looks like:

meta = {"runTime": 3922000, "distance": 85132, "duration": 4049000, "fuelUsed": 19.595927498516176}

To select the trip which has largest value divided by "distance" and "runTime", I run query:

select MAX(tp."meta"->>'distance'/tp."meta"->>'runTime') maxkph FROM "Trip" tp

but I get ERROR:

/* ERROR:  operator does not exist: unknown / jsonb LINE 1: MAX(tp."meta"->>'distance'/tp."meta"...

I also tried:

select MAX((tp."meta"->>'distance')/(tp."meta"->>'runTime')) maxkph FROM "Trip" tp

but get another ERROR:

/* ERROR:  operator does not exist: text / text LINE 1: ...MAX((tp."meta"->>'distance')/(tp."meta...

Could you please help me to solve this problem?

Pavel Stehule · Accepted Answer · 2020-04-30 15:23:09Z

2

There is not operator div for jsonb values. You have to cast a values on both sizes to some numeric type first:

MAX( ((tp."meta"->>'distance')::numeric) / ((tp."meta"->>'runTime')::numeric) ) maxkph

answered Apr 30, 2020 at 15:23

Pavel Stehule

46.7k6 gold badges104 silver badges103 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2020-04-30 15:24:01Z

1

Try using parentheses:

MAX( (tp."meta"->>'distance') / (tp."meta"->>'runTime') ) as maxkph

Your second problem suggests that these values are stored as strings. So convert them:

MAX( (tp."meta"->>'distance')::numeric / (tp."meta"->>'runTime')::numeric ) as maxkph

answered Apr 30, 2020 at 15:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Many thanks for check my question. I have tried your method, but get another error. I have modified my question.