All pumpkins may be eaten, but there is a big difference among varieties. Decorative pumpkins, which American children and patient parents carve just before Halloween, are grown with color, structural strength, a flat bottom, and a sturdy stem as their main attributes. The flesh tends to be bland, watery, and fibrous. No one cares because they're going to be carved and smashed in the street (or disposed of properly).
Culinary pumpkins - not just pie pumpkins, because some people make pickles, preserves, and savory dishes, as well - have firmer flesh and a sweeter taste. There are many varieties of culinary pumpkins, and heirloom varieties are highly prized for their taste and texture. Among the best are the Small Sugar, Winter Luxury, Cheese, Golden Cushaw, and Rouge Vif d'Etampes.
Unless you're growing your own, you may have a hard time finding anything other than the Small Sugar pumpkin, which is also known as the New England Pie, Northern Pie, and Sugar Pie. The Cheese and Golden Cushaw do not look like classical pumpkins, but may be easier to find in areas with hotter climates. In general, these are sweeter than the Small Sugar pumpkins, and are more often used in commercial canning. Unless you live in New Orleans or a snobby neighborhood, the Rouge Vif d'Etampes is quite likely to be called a Cinderella or Deep Red pumpkin.
Some purists may disagree, but an awful lot of people believe that pumpkin is one of the few foods that is better canned than fresh - that it is more consistent and has a better texture. It certainly is easier to use.