3

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)

Improve this question
3
  • Use the :: operator (evt.result::int = campaign_items.id) Commented Jan 11, 2017 at 10:34
  • postgresql.org/docs/current/static/… Commented Jan 11, 2017 at 10:50
  • You can cast it by CAST(evt.result as int) Commented Jan 11, 2017 at 10:57

2 Answers 2

Reset to default
5

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I run using your suggestion and got below error ERROR: invalid input syntax for integer: "convRepeatDelay"
Its working without any error,but there is no changes in database :->Query returned successfully: 0 rows affected, 275 msec execution time.
specifically add some sample data from both tables
glad to have been of help.
1

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 ;-)

Improve this answer

6 Comments

ERROR: invalid input syntax for integer: "convRepeatDelay"
Can you be a bit more precise on what is going wrong? It seems the result column is from the events table and not the campaign_items table, I update this :-)
Yes you right "result" & "id" are two column from" events" & "campaign_items" table respectively.
Oh, or that means that one of you result values is "convRepeatDelay". Can you check this with "SELECT * FROM events WHERE result = 'convRepeatDelay';"?
Ohh yeah..Thats right even i am amazed..So what should i do now?
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.