CODEWITHSUNDEEP
sqlpostgresqlspring-bootpostgresql-12
Utkarsh Mishra
Utkarsh Mishra

Reputation: 17

How to implement LIKE condition in postgresql version 12?

Select COUNT(*) from Table_one where timestamp LIKE '%2020-03-04%' AND speed_type = 'SPEED';

This query is showing error when I am implementing it via spring boot framework so I checked it out on postgresql and still it's showing error.

The error is this:-

 ERROR:  operator does not exist: date ~~ unknown
LINE 1: Select COUNT(*) from table_one where timestamp LIKE '2020-0...
                                                        ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 49

Upvotes: 0

Views: 81

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Use date/time functions! Don't convert to a string!

where timestamp >= '2020-03-04' AND
      timestamp < '2020-03-05' AND
      speed_type = 'SPEED'

Not only does this prevent unnecessary type conversion. But it is also immune to any internationalization settings that might affect conversion to a string (that might depend on the database).

It can also use appropriate indexes that include timestamp. And the optimizer has better information, which can improve the query plan (although the plan in this case is, admittedly, simple).

Upvotes: 2

Nick
Nick

Reputation: 147166

You need to cast your timestamp column as VARCHAR to allow it to be compared to a character string:

Select COUNT(*) 
from Table_one 
where CAST(timestamp AS VARCHAR) LIKE '%2020-03-04%'
  AND speed_type = 'SPEED';

Demo on dbfiddle

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

Because you're comparing a timestamp value with a string type value. So, a conversion needed such as 

timestamp::text LIKE '%2020-03-04%'

Upvotes: 0

Related Questions