• Home »
  • Insights »
  • No operator matches the given name and argument type(s). You might need to add explicit type casts.

No operator matches the given name and argument type(s). You might need to add explicit type casts.

When working on writing some directions for someone at work I ran into a PostgreSQL command line query error. I have much more experience using MySQL so my initial Postgres commands always mimic what I would do in MySQL. Anyhow when attempting to run a SELECT statement matching contents of a specific column I received the error below. After a little research I came up with the answer fairly easily but wanted to note here to help others out and serve as a reference point if I forgot the proper PSQL query in the future. Below I describe the query that caused the error, the error returned by Postgres, and the proper query to use when attempting to match a variable in a Postgres SELECT statement.

Postgres Error When Attempting To Run A SELECT Statement Using LIKE:

db=# select * from events where timestamp LIKE '%01-25-2010%';
ERROR:  operator does not exist: timestamp with time zone ~~ unknown
LINE 1: select * from events where timestamp LIKE '%01-25-2010%';
                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

It appears that in PostgreSQL 8.3+ you must specify data type for the output when using LIKE. So you can still do a SQL SELECT statement that equals a specific value similar to the below but when you are using LIKE you must specify “CAST(column as TEXT)” to return values.

Normal SELECT Statement Using PostgreSQL 8.3:

db=# select * from event_actions where event_id = '448307';
 id  | user_id | event_id | description |         created_at         |         updated_at
-----+---------+----------+-------------+----------------------------+----------------------------
 654 |     276 |   448307 | test_alarm  | 2010-02-02 02:48:40.610155 | 2010-02-02 02:48:40.610155
(1 row)

PostgreSQL 8.3 SELECT Statement Using LIKE To Match Column Items:

db=# select * from events where CAST(timestamp as TEXT) like '2010-01-26 10:%';
   id   | user_id |           timestamp           |       event_type       | event_id |       timestamp_server        | call_center_response
--------+---------+-------------------------------+------------------------+----------+-------------------------------+----------------------
 448218 |     319 | 2010-01-26 10:06:53+00        | AccessMode             |     3251 | 2010-01-26 10:07:12.694941+00 |
 448219 |      41 | 2010-01-26 10:09:41.327604+00 | DeviceAvailableAlert   |    11875 | 2010-01-26 10:09:41.412268+00 |
 448220 |      41 | 2010-01-26 10:09:41.321084+00 | GatewayOnlineAlert     |    18097 | 2010-01-26 10:09:41.490391+00 |
 448221 |      41 | 2010-01-26 10:09:43.149577+00 | GatewayOnlineAlert     |    18098 | 2010-01-26 10:09:43.193929+00 |
 448222 |     195 | 2010-01-26 10:17:42.643144+00 | DeviceUnavailableAlert |    10110 | 2010-01-26 10:17:42.782372+00 |
 448223 |     195 | 2010-01-26 10:18:41.692667+00 | DeviceAvailableAlert   |    11876 | 2010-01-26 10:18:41.739818+00 |
 448230 |     275 | 2010-01-26 10:19:06+00        | BatteryChargeComplete  |     7410 | 2010-01-26 11:31:30.767173+00 |
 448236 |     188 | 2010-01-26 10:38:47+00        | BatteryChargeComplete  |     7411 | 2010-01-26 12:03:15.279227+00 |
 448251 |     343 | 2010-01-26 10:19:55+00        | BatteryUnplugged       |     5282 | 2010-01-26 12:24:35.388613+00 |
 448270 |     292 | 2010-01-26 10:53:39+00        | BatteryPlugged         |    27255 | 2010-01-26 13:30:04.470422+00 |
 448298 |     268 | 2010-01-26 10:58:16+00        | BatteryChargeComplete  |     7416 | 2010-01-26 14:53:51.890409+00 |
 448313 |     192 | 2010-01-26 10:21:18+00        | BatteryChargeComplete  |     7418 | 2010-01-26 15:58:34.835773+00 |
(12 rows)

So in the above you can see that I am using LIKE to match on timestamp. In this particular case I wanted to return all timestamps from 01-26-2010 or all timestamps LIKE ‘%2010-01-26%’. You used to be able to just use “LIKE ‘%2010-01-26%’” but as of PostgreSQL 8.3 you are required to CAST the type as TEXT as displayed in the above example.

PostgreSQL 8.4 Official Documentation - Volume III. Server Programming (Paperback)

By (author): The PostgreSQL Global Development Group


List Price: $30.99 USD
New From: $28.96 USD In Stock
Used from: $7.95 USD In Stock

PostgreSQL 8.4 Official Documentation - Volume IV. Reference (Paperback)

By (author): The PostgreSQL Global Development Group


List Price: $35.99 USD
New From: $35.99 USD In Stock
Used from: $5.94 USD In Stock

Share