Postgresql: How to find exact value of numeric column?

Question

I have a table with numeric coloumn, named 'debt'. Values: 350, 3500, 350000. I want to find value just for 350, not others.

When I tried with this:

 SELECT * FROM rekkor WHERE debet::TEXT LIKE '350%'

Resulting all values.

But when I changed to:

SELECT * FROM rekkor WHERE debet::TEXT = '350'

there was no result at all.

What is the right script?

Peter M. Elias · Accepted Answer · 2016-02-27 01:28:53Z

3

If it's column type NUMERIC just do a straight number comparison. No need to convert it to a text value. Is there a reason you're converting it?

Why not just:

SELECT * FROM rekkor WHERE debt = 350;

answered Feb 27, 2016 at 1:28

Peter M. Elias

1,19410 silver badges23 bronze badges

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

5 Comments

bandungeuy Over a year ago

I'm sorry for my bad explanation. I have one "keyword" variable (let say: $find), so $find search for multiple values on different columns. I want to search $find on numeric coloumn and non-numeric coloumn. So I use ::TEXT. Is it wrong?

Peter M. Elias Over a year ago

Okay I think I get what you're saying. You're doing comparisons on a variable $findthat might be a string or it might be a number you converted to a string. Short answer: don't do that. (unless you have a great reason). I say this because it's difficult to design a predictable query on a value that could vary so widely in type and meaning. Just write separate queries to look for different things.

bandungeuy Over a year ago

I want to build a simple search machine. User just input just one keyword, and that will give them all result from all kind coloumn on one table. Do you have any better ideas for this need? Thank you so much.

Jim Nasby Over a year ago

It's still dangerous, but if you want an exact value don't put a % in the column. Oh, and no matter what you do the performance of this is probably going to stink on any sizable table. Really, you just need to make your search data type aware. Doing string searches with LIKE on numbers is nonsensical, and I bet it's not long before users want things like "debit < 500', which you definitely WON'T pull off correctly by converting the column to text. BTW, pg_typeof() will tell you the type of a column.

bandungeuy Over a year ago

Thank @JimNasby for your suggestion. In this case, users want to search a keyword that maybe a number but maybe not. So I could wrote: select * from rekkor where desc like '%$find%' OR debt::TEXT= '$find'. But that''s tnot the solution because no result at all.

Collectives™ on Stack Overflow

Postgresql: How to find exact value of numeric column?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related