Why same postgres query is not working for double digit number?

Question

I am using postgres database for my application.

I have below data

    create table contacts (
        id int,
        contact_data jsonb
    );
    
    insert into contacts values
    (1,   '{
        "tags": [
            "MOCK_DATA (4)"
        ],
        "Email": "[email protected]",
        "reach": false,
        "Gender": "Male",
        "Interest": [
            "Dance"
        ],
        "Last Name": "Kynman",
        "First Name": "Nicko"
    }'),
    (2,   '{
        "ltv": "6",
        "City": "Bengaluru",
        "Email": "[email protected]",
        "State": "Karnataka",
        "Country": "India",
        "latitude": 12.9715987,
        "Last Name": "World",
        "longitude": 77.5945627,
        "First Name": "Helo"
    }'),
    (3,   '{
        "ltv": "11",
        "Email": "[email protected]",
        "reach": false,
        "Gender": "Female",
        "Country": "United States",
        "latitude": "37.09024",
        "Last Name": "Fortye",
        "longitude": "-95.712891",
        "First Name": "Sissie"
    }');

    Select * from contacts;
    
    +====+==================================================================================================================================+
    | id | contact_data                                                                                                                     |
    +====+==================================================================================================================================+
    | 1  | {"tags": ["MOCK_DATA (4)"], "Email": "[email protected]", "reach": false, "Gender": "Male", "Interest": ["Dance"], "Last... |
    +----+----------------------------------------------------------------------------------------------------------------------------------+
    | 2  | {"ltv": "6", "City": "Bengaluru", "Email": "[email protected]", "State": "Karnataka", "Country": "India", "latitude": 12.9715987, "L... |
    +----+----------------------------------------------------------------------------------------------------------------------------------+
    | 3  | {"ltv": "11", "Email": "[email protected]", "reach": false, "Gender": "Female", "Country": "United States", "latitude": "3... |
    +----+----------------------------------------------------------------------------------------------------------------------------------+

I am trying to fetch id who has ltv between 2 to 9. and it's giving 1 record. which is correct

    SELECT id from contacts where ( contact_data->> 'ltv' > '2' and contact_data->> 'ltv' < '9' );
    +====+
    | id |
    +====+
    | 2  |
    +----+

Issue:

Now i am trying between 2 to 12, which should give 2 records, but it's giving 0 data.

SELECT id from contacts where ( contact_data->> 'ltv' > '2' and contact_data->> 'ltv' < '12' );

Can anyone tell me what's the issue here. why I am not getting the value? here is sql-fiddle

Slava Rozhnev · Accepted Answer · 2021-02-02 16:30:39Z

1

In PostgreSQL ->> operator will return a text value. So you have too cast it in respective type.

Try below query:

SELECT id 
from contacts 
where ( 
    (contact_data->> 'ltv')::int > 3 and 
    (contact_data->> 'ltv')::int < 12 
);

DEMO Only thing you have to take care is, itv must contain integer value only, otherwise casting will throw error.

edited Feb 2, 2021 at 16:30

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

answered Jan 15, 2021 at 5:51

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Comments

Islingre · Accepted Answer · 2021-01-15 05:45:18Z

1

Have a look at the data type of ltv. They are strings and not numbers, so it is ordered lexicographically.

Cast it in the query to give it the meaning you want it to have.

EDIT: remove using number in the JSON, as retrieving a number field form JSON seems to not give a numeric value.

edited Jan 15, 2021 at 5:45

answered Jan 15, 2021 at 5:30

Islingre

2,37810 silver badges20 bronze badges

5 Comments

Sangram Badi Over a year ago

i tried with number but still it's not showing any data

Islingre Over a year ago

What exactly did you try?

Islingre Over a year ago

Try the casting after the JSON access, as this table might say you get always text if you retrieve something from a JSON: postgresql.org/docs/current/…

Sangram Badi Over a year ago

So at the time of insertion I inserted LTV as number. and tried below query SELECT id from contacts where ( contact_data->> 'ltv' > 2 and contact_data->> 'ltv' < 12 );

Akhilesh Mishra Over a year ago

this will also now work. You should try like this SELECT id from contacts where ( (contact_data->> 'ltv')::int > 3 and (contact_data->> 'ltv')::int < 12 ); . No matter you are providing value 6 or "6". Only thing never store a non interger value in ltv

Collectives™ on Stack Overflow

Why same postgres query is not working for double digit number?

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related