Explicit type conversion in postgreSQL

Question

I am joining the two tables using the query below:

update campaign_items
set last_modified = evt.event_time
from (
    select max(event_time) event_time
        ,result
    from events
    where request = '/campaignitem/add'
    group by result
    ) evt
where evt.result = campaign_items.id

where the result column is of character varying type and the id is of integer type But the data in the result column contains digits(i.e. 12345)

How would I run this query with converting the type of the result(character) into id (integer)

Answer 1

Well you don't need to because postgresql will do implicit type conversion in this situation. For example, you can try

 select ' 12 ' = 12

You will see that it returns true even though there is extra whitespace in the string version. Nevertheless, if you need explicit conversion.

 where evt.result::int = campaign_items.id 

According to your comment you have values like convRepeatDelay, these obviously cannot be converted to int. What you should then do is convert your int to char!!

 where evt.result = campaign_items.id::char

Answer 2

There are several solutions. You can use the cast operator :: to cast a value from a given type into another type:

WHERE evt.result::int = campaign_items.id

You can also use the CAST function, which is more portable:

WHERE CAST(evt.result AS int) = campaign_items.id

Note that to improve performances, you can add an index on the casting operation (note the mandatory double parentheses), but then you have to use GROUP BY result::int instead of GROUP BY result to take advantage of the index:

CREATE INDEX i_events_result ON events_items ((result::int));

By the way the best option is maybe to change the result column type to int if you know that it will only contain integers ;-)