0

I have a query that must set a value if the number is between 2 values, but the output is not ok, I think because that column is a string. Any way to do it even it's string? (In output I have value as , 5 witch is not ok). All the values that are incorrect are Integer.

SET lkp_age_category_id = 7
WHERE
   age BETWEEN '26' and '35.99';
Beusebiu
  • 1,433
  • 4
  • 23
  • 68
  • Typically, age isn't an integer (or string) value in the database, it's a date field. You should be able to cast `age` to an integer, but I'm not sure of the syntax for that in PGSQL. Is the query you posted the Laravel Eloquent code? Or are you running a raw query here? – Tim Lewis Mar 25 '20 at 14:53
  • It's a raw query, the goal is to do this interrogation without changing it's type, if it's possible. I used this type of query for multiple categories, and on output, I have some integer value that are not in the correct category, and I assume that is because I have age as string. – Beusebiu Mar 25 '20 at 14:56
  • 1
    Casting is a temporary type change, something like `SET lkp_age_category_id = 7 WHERE CAST(age AS INTEGER) BETWEEN 26 AND 35.99;` would allow your query to recognize `age` column as an integer and properly handle between 2 numbers. – Tim Lewis Mar 25 '20 at 15:00
  • 1
    Cool, works, but as a Number . (integer,float,data, doesn't). Thank you! – Beusebiu Mar 25 '20 at 15:21
  • I don't see the edit button for my comment. So. NOT Number, " NUMERIC " is the correct type. – Beusebiu Mar 26 '20 at 07:05
  • You can only edit a comment for 5 minutes after your post it :) Btw, you are more than welcome to add a self-answer, and when the system allows it, accept it (via the checkmark) to properly close this question. – Tim Lewis Mar 26 '20 at 13:33
  • You can post this answer, to approve it, because it was your initial query, if is ok, thanks! – Beusebiu Mar 26 '20 at 13:47

1 Answers1

0

I guess, only working on the SQL side, you could cast the values right into the query, e.g.:

SET lkp_age_category_id = 7
WHERE age BETWEEN '26'::float AND '35.99'::float;

Also check this answer https://stackoverflow.com/a/13809603/917617.

steppo
  • 583
  • 3
  • 20
  • I tried this,but I got an error on BETWEEN not sure why, so I tried with (CAST AS NUMBER insted), to make it work. – Beusebiu Mar 25 '20 at 15:27
  • I assumed a table create table `test` as follows: `CREATE TABLE test (lkp_age_category_id varchar, age double precision);`. Works in this case. Feel free to share the table schema if you like to further check the issue. – steppo Mar 27 '20 at 14:41