Re: MySQL search query is not executing in Postgres DB

On Fri, Feb 17, 2012 at 17:13, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> Why not use:
> " SELECT * FROM <Table-name> WHERE CAST(Table.ID as TEXT) LIKE '1%' "
>
> as the MySQL query also?  MySQL supports the CAST function.

Nope, trying to use CAST() in an interoperable manner is a lost cause.
Sadly MySQL and PostgreSQL don't agree over the cast types. MySQL's
CAST doesn't recognize varchar or text. Both support CHAR, but they
work differently (MySQL's char behaves like PostgreSQL's varchar):

CAST('foo' as char);
MySQL: 'foo'
PostgreSQL: 'f'

CAST('foo' as char(10))
MySQL: 'foo'
PostgreSQL: 'foo ' (padded with spaces!)

The only 3 casts that *seem* to behave similarly are time, date and decimal.

You could abuse the concat function (present in PostgreSQL 9.1+) for
this purpose, but it's ugly:
SELECT ... WHERE concat(Table.ID) LIKE '1%'

Regards,
Marti