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?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.