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: $23.98 USD In Stock
Used from: $9.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: $6.95 USD In Stock

DeliciousStumbleUponDiggTwitterFacebookRedditLinkedInEmail
Tags: , , , , , , , , , , , , ,
2 Responses to “No operator matches the given name and argument type(s). You might need to add explicit type casts.”
  1. shrenik says:

    thanks. it works for me.

    [Reply]

    alex Reply:

    Hello Shrenik,

    Awesome. Great to hear. Thanks for taking the time to leave feedback.

    Thanks.
    alex

    [Reply]

  2.  
Leave a Reply

*Type the letter/number combination in the abvoe field before clicking submit.

*