Postgresql ERROR: operator does not exist: date ~~ unknown

Question

When I do this query, I have no problems:

SELECT a.value, b.label AS campo, 'date' AS tipo
FROM contenido.field_value_textarea a 
JOIN estructura.field b ON a.field=b.id 
WHERE a.value LIKE '%aaa%'

contenido.field_value_textarea is character varying(2000)

But if I try to select from:

contenido.field_value_fecha which type is date I got this error message:

ERROR: operator does not exist: date ~~ unknown

What I'm trying to do is searching between different tables, each query select FROM it's table. Some tables use text values, textarea values, integer values, and it works, but when the value is date all fails. What can I do?

EDIT: By the way, my date values are like this: 2009-05-01

Answer 1

The ~~ operator is actually the LIKE operator.

You are trying to use an expression that looks like:

contenido.field_value_fecha.value LIKE '%aaaa%'

That is, you're trying to compare a date with a string (which, without the adequate context, is considered to be of type 'unknown'), and decide if the date looks like something.

If you actually want to do such a comparison, you need to convert the date to a string, which can be done by means of:

contenido.field_value_fecha.value::text LIKE '%aaaa%'

or (using standard SQL):

CAST(contenido.field_value_fecha.value AS text) LIKE '%aaaa%'

This will be syntactically correct... Whether it is meaningful or not, is a different part of the story.