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.

See larger image PostgreSQL 8.4 Official Documentation - Volume III. Server Programming (Paperback) By (author) The PostgreSQL Global Development Group List Price: $30.99 USD New From: $25.45 In Stock Used from: $8.96 In Stock

See larger image 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 In Stock Used from: $11.46 In Stock

This entry was posted on Monday, February 8th, 2010 at 11:16 PM and is filed under Insights. You can follow any responses to this entry through the RSS 2.0 feed. You can skip to the end and leave a response. Pinging is currently not allowed.