MySQL search query is not executing in Postgres DB

Question

In MySQL the below query is executing properly.

SELECT * FROM <Table-name> WHERE (Table.ID LIKE '1%')

But when I try to execute the above query in Postgres, I get the following exception

"org.postgresql.util.PSQLException: ERROR: operator does not exist: integer ~~ unknown Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts".

If I convert the same query

SELECT * 
FROM <Table-name> 
WHERE CAST(Table.ID as TEXT) LIKE '1%'

This gets executed directly in Postgres DB. But I need some query which implicitly type cast in DB, which allows me to execute the MySQL query without any exception. Because I remember there is a way for integer to boolean implicit type cast.

Thanks in advance.

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Feb 17, 2012 at 9:25
As far as I can tell implicit casts were removed in PostgreSQL 8.3. What version are you on? — John P
– John P, Commented Feb 17, 2012 at 9:42
It those are really numbers, searching with LIKE does not make sense. If those aren't "real" numbers than store them in a varchar column. — user330315
– user330315, Commented Feb 17, 2012 at 11:10

A.H. · Accepted Answer · 2012-02-17 10:41:39Z

2

If you search this site with [postgresql] explicit type casts you will get enough information to solve your problem.

answered Feb 17, 2012 at 10:41

A.H.

66.7k16 gold badges96 silver badges132 bronze badges

Add a comment |

Collectives™ on Stack Overflow

MySQL search query is not executing in Postgres DB

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related