Connotations can include being stupid as well -- which is why Ron Wesley is offended (at the end of Azkaban) when Ginny names his replacement owl Pigwidgeon, and he quickly changes it to Pig. Its original sense, though, was a type of fairy or imp, of the sort that's the less effective in its mischievousness than other, more dangerous varieties. It's in this sense that Elizabethan poet Michael Drayton in "Nymphidia," named a fairy knight Pigwiggen, who acts as a not entirely effective lookout against Oberon when Titania has an adulterous tryst. The origins of the name aren't clear, but there's some speculation that the pig- part may be an altered pug, another kind of fairy and probably the same word as Puck (who also shows up in "Nymphidia"), while the alteration over the centuries from wiggen to widgeon may have been after the waterfowl called a widgeon, which is not noted for its intellect even in bird terms.
I totally mishandled it -- nothing more than a noodle-headed, ham-handed, pigwidgeon of an ex-boyfriend, I am.