Comparing different data-types in PostgreSQL.

Question

it's been a while since I have made one of my "Converting MySQL to PostgreSQL" posts.

So, today's problem is as follows:

The original MySQL queries involve where clauses which look a bit like the following:

 WHERE id LIKE '%6%' 
 OR createdtime LIKE '%6%' 
 OR modifiedtime LIKE '%6%' 
 OR start_date LIKE '%6%' 
 OR end_date LIKE '%6%' 
 OR sc_related_to LIKE '%6%' 
 OR tracking_unit LIKE '%6%'
 OR message LIKE '%6%'

This query is part of a system wide search. In this case, the system is searching for 6, had I asked it to search for something else, like say the word user, instead of %6%, we'd have %user%.

Now, the problem is that, the above data-types are not always strings. Integer fields like id and date/time fields like createdtime are being compared to strings. In MySQL, this seems to be okay, but grumpy PostgreSQL gets grumpy when it sees this query.

I know that for some fields, I can use the to_char function, so, for example, part of the clause in PostgreSQL might look like this:

 to_char(id, '999') LIKE '%6%'

Unfortunately, I can't just go throught the queries and add the to_char to each applicable field because of the PHP backend. This is what the PHP code for generating the WHERE clause looks like:

 $where .= $tablename.".".$columnname." LIKE '". formatForSqlLike($search_val) ."'";

Note: It's part of a loop, so the above line generates all of the individual comparisons.

So, even if I can get around the type comparison with to_char, I can't implement it because to_char might need a specific 2nd parameter for a different data-type and even if I could use the same parameter for all data-types, some of the data-types would be strings, and passing a string to to_char throws an error.

So, I need a way to either get PHP to determine the column type and use the right to_char(or don't use it at all) accordingly or I need to get PostgreSQL to compare different data-types.

Thanks for all of your help, have a good day!

What sense does it make to use LIKE against a date column? — user330315
– user330315, Commented Dec 7, 2011 at 16:05
I don't know...I didn't write the code. :( I guess, it's there so that if a user searched a date it could get some results, but I don't know how well LIKE works with dates. — zermy
– zermy, Commented Dec 7, 2011 at 16:13
PostgreSQL gets grumpy when it sees this query... which speaks well of PosgresSQL. — leonbloy
– leonbloy, Commented Dec 7, 2011 at 17:06
Oh Postgres is fine, it just doesn't put up with as much silliness as mySQL.. — Mike Christensen
– Mike Christensen, Commented Dec 7, 2011 at 17:12

Mike Christensen · Accepted Answer · 2011-12-07 17:07:15Z

4

You could just cast everything to a string, such as:

$where .= $tablename.".".$columnname."::text LIKE '". formatForSqlLike($search_val) ."'";

If you don't want to change the PHP code, another idea is to create a VIEW that does the cast, such as:

CREATE OR REPLACE VIEW TableName AS
   SELECT
     createdtime::text,
     modifiedtime::text,
     start_date::text,
     end_date::text,
     sc_related_to::text,
     tracking_unit::text,
     message::text
   FROM RealTable;

Then your PHP code would just use this view instead of the table, and see everything as text.

However, I believe your technique here is really not going to scale well. Every search will probably be doing a sequential scan of the entire table, since LIKE clauses cannot be indexed.

Your best bet is to re-design this using a FULLTEXT search index, which will be way more versatile as well as lightning fast.

edited Dec 7, 2011 at 17:07

answered Dec 7, 2011 at 16:57

Mike Christensen

91.8k52 gold badges223 silver badges349 bronze badges

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

3 Comments

zermy Over a year ago

Oh my, thanks for that. It was silly of me not to try casting it like that. The FULLTEXT search seems really interesting, but unfortunately, implementing it in this system would probably be very time consuming and painful. I will, however, keep that in mind when and if I ever have to design a search from scratch.

Mike Christensen Over a year ago

Yup! But at least now you can bring it up to the powers that be, and when it all comes crashing down say you told them so.

Craig Ringer Over a year ago

If you use the standards-compliant CAST(colname AS text) then you'll have less porting work to do, too.

Collectives™ on Stack Overflow

Comparing different data-types in PostgreSQL.

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related