PostgreSQL JsonB can't use IN operator on numbers

Question

I have a table with 4 columns and the following types.

|id          |businessId|type       |data |
|VARCHAR(100)|BIGINT    |VARCHAR(50)|jsonb|

My query needs to get all items with type 'XXX' and field from the data depID having one of the values 1 2 or 3.

This is how my query looks at the moment

select * from test_table where type='XXX' and data ->> 'depID' IN (1, 2, 3);

The above query give the following error:

[42883] ERROR: operator does not exist: text = integer Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

depID is of type number in the json.

Same query works just fine for string values, for example:

select * from test_table where type='XXX' and data ->> 'loc' IN ('NYC', 'BST', 'None');

loc is of string type in the json.

I understand that it cant figure that the depID is of type number, but I am not sure how to specify it.

How to specify that the type of depID is number?

EDIT:

It turns out I was reading old specifications. I am using Postgres 11.4

The correct syntax is the following.

select * from test_table where type='XXX' and (data->>'depID'):NUMERIC IN (1, 2, 3);

The ->> operator has returns string. This explains why string fields work without casting.

How to specify that the type of depID is number?

Borislav Stoilov
– Borislav Stoilov

2020-09-30 16:30:42 +00:00
Commented Sep 30, 2020 at 16:30 — Borislav Stoilov
– Borislav Stoilov, Commented Sep 30, 2020 at 16:30

Laurenz Albe · Accepted Answer · 2020-09-30 16:39:05Z

1

The ->> operator has text as result data type, so it cannot return a number.

It would be possible to add a special access operator for numbers, but that would just clutter the ooerator namespace needlessly, since it can be done with a simple type cast:

CAST (data ->> 'depID' AS integer) IN (1, 2, 3)

answered Sep 30, 2020 at 16:39

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

2 Comments

Borislav Stoilov Over a year ago

you are right, the default data type is string. Your solutions works. What is the difference between CAST(.. AS integer) and (..)::NUMERIC. ?

Laurenz Albe Over a year ago

:: is PostgreSQL's shortcut for CAST, so no difference there. If the numbers are not necessarily integers, use numeric by all means.

Collectives™ on Stack Overflow

PostgreSQL JsonB can't use IN operator on numbers

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related