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

Can you show us the SQL that is generating the error, and the schema of the table? — jmelesky
– jmelesky, Commented Feb 24, 2017 at 23:14
Arghhhh, I got it, I had to CAST my date values as TEXT then LIKE as the given text. I got it from here: stackoverflow.com/questions/20849071/… sorry — pmiranda
– pmiranda, Commented Feb 24, 2017 at 23:15
Using text matching on date values sounds error-prone to me. You might be better off using some of the postgres date functions. I'm thinking particularly of date_trunc and date_part. — jmelesky
– jmelesky, Commented Feb 24, 2017 at 23:28
Ok, I will see what can I do there. Those queries weren't meant to be used with dates, I had to add a search for dates too, so I was trying to reproduce the queries but in date. — pmiranda
– pmiranda, Commented Feb 24, 2017 at 23:48

joanolo · Accepted Answer · 2017-02-24 23:49:05Z

28

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.

edited Feb 24, 2017 at 23:49

answered Feb 24, 2017 at 23:18

joanolo

6,3881 gold badge33 silver badges40 bronze badges

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

2 Comments

pmiranda Over a year ago

Hey, much better than my solution: WHERE CAST(a.value AS TEXT) LIKE '%aaa%' . Now I did it like you said: WHERE a.value::text LIKE '%aaa%' and it works ok too. Thanks!

mu is too short Over a year ago

@pmirnd cast(a.value as text) and a.value::text are the same thing but :: is PostgreSQL-specific whereas cast(...) is standard SQL.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related