Search code examples
sqlpostgresqlspring-bootpostgresql-12

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
Solution

  • 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