I use "masonry bits" on unglazed clay (terra cotta) pots. For example,
http://www.homedepot.com/p/Bos... Go slow, especially as you finish the hole. Too much pressure will break away large chunks as the last bits are cut away, and if you press too hard, you can smack the thing with the drill chuck and smash the pot. Don't ask me how I know this.
For glazed clay pots, or for glass, diamond tipped hole saws are great, and carbide "glass drill bits" like the bosch set that ge1836 linked to also work reasonably well.