It'll almost certainly be put in once you're asleep. Depending on the operation, it might be removed at the end of the procedure or shortly afterwards, or it might have to stay in for a day or more.
The actual insertion is not particularly painful anyway, especially as they usually use local anaesthetic gel to lubricate it. Once the catheter is in, they aren't painful, though some people find them irritating and they can make you feel like you need to pee even though you don't/can't because the catheter is draining it all. Nothing much to worry about!