PostgreSQL: Select data with a like on timestamp field
Try this:
SELECT my_table.id
FROM my_table
WHERE CAST(my_table.date_checker AS VARCHAR) LIKE '2011-01-%';
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.
Perhaps the date_trunc
function would be more to your liking:
... WHERE date_trunc('month', my_table.date_checker) = '2011-01-01'
You can also put an index on that expression, if needed.