They are sore going in if you are fully conscious and aware - but then, I imagine you'll have it put in whilst under so won't feel that. I was hanging onto the bed rails biting down on a bit of tissue, but then I was in acute urine retention with a significantly narrowed, scarred spasming urthera and in a lot of pain anyway.. Was a nightmare as had my knees under my chin, doctor forcing the tube up me and telling me to push down and calm down, and a cleaner stuck her head in the room halfway through!!
While it's in you shouldn't be too bothered by it, just remember to keep a hold off it if it's on a short lead and you move (they sometimes give long leads so you can move freely at nighttime) - I was showering with a particularly short lead and full bag, dropped the bag and oh the pain!
Can sometimes annoy while it's in - I did feel at nighttime a constant need to wee, but codeine helped (as will tiredness from surgery), relaxing your urethra will help (feels like you are weeing but you aren't). Having one in whilst sitting might feel funny too at first, especially if in a wheelchair etc where the wheels might bump into hard surfaces, but again not too painful as such. More of a weird feeling.
Coming out is a doddle, they just ask you to breathe out, they deflate the balloon thing that holds it in your bladder, and the tube slides out, no pain really :) Just remember to drink lots of fluids after!
Best of luck x