I am trying to select data from a table, using a "like" on date field "date_checked" (timestamp). But I have this error :
SQLSTATE[42883]: Undefined function: 7 ERROR: operator does not exist: timestamp without time zone
My request is :
SELECT my_table.id
FROM my_table
WHERE my_table.date_checker LIKE '2011-01-%'
I don't want to use :
SELECT my_table.id
FROM my_table
WHERE my_table.date_checker >= '2011-01-01 00:00:00'
AND my_table.date_checker < '2011-02-01 00:00:00'
It's all very well not "wanting to use" < and > with timestamps, but those operators can be converted into index scans, and a string-match... well, it can, but EWWWW.
Well, the error is occurring because you need to explicitly convert the timestamp to a string before using a string operation on it, e.g.:
date_checker::text LIKE '2011-01-%'
and I suppose you could then create an index on (date_checker::text)
and that expression would become an index scan but.... EWWWW.