Generally NO. Moisture within the pulp of a cactus is very acidic and many cacti contain toxic alkaloids. You can, however, eat the fruit.
The moisture is acidic because of the way many succulents, including cacti, carry on photosynthesis, the process by which carbon dioxide and water are turned into carbohydrates.
Most plants have their pores (stomates) open during the day to take in carbon dioxide, and use sunlight as a catalyst for the reaction: Carbon dioxide + water sugar + oxygen. But in the desert, plants with pores open during the hot days, lose much water through evapotranspiration.
So, succulents use a modified version of photosynthesis called CAM (Crassulacean Acid Metabolism). CAM plants open their stomates only at night when it is cooler so there is less evapotranspiration. Because there is no sunlight to act as a catalyst, carbon dioxide is stored as an organic acid, principally Malic Acid (C4H6O5). Carbon dioxide is gradually released from the acid during the next day. CAM plants use about one-tenth the water to produce each unit of carbohydrate compared to standard photosynthesis. The price: a much slower growth rate.
Many plants contain malic acid, but usually in lesser quantities than found in cacti. Also cooking generally destroys the acid.
Besides malic acid, succulents produce Oxalic Acid (C2H2O4), which is toxic, as another product of photosynthesis. “Its chief function seems to be sequestering metals, principally calcium. Calcium oxalates often occur as crystalline minerals within the cactus pulp. Their function seems to be aiding structural integrity and enzymatic processes. In fact two crystalline calcium oxalate minerals have been identified in all cacti tested: CaC2O4.2H2O (weddellite) and CaC2O4.H2O (whewellite).” [Source: Plant Physiology, February 2002, Vol. 128, pp. 707-713.] Oxalates are also formed with heavy metals such as copper, perhaps to reduce toxicity to the plant.
Oxalic acid is toxic to humans because it combines with calcium in our bodies to produce calcium oxalates which clog up our kidneys.
So, what about the barrel cactus. Can’t we get water from those? Did you bring along a machete and solar still?
The Seri Indians sometimes used the Fishhook barrel (Ferocactus wislizeni) for emergency water. However, drinking the juice on an empty stomach often caused diarrhea, and some Seri report pain in their bones if they walk a long distance after drinking the juice. The Seri called the Coville barrel (Ferocactus emoryi), “barrel that kills” because eating the flesh of the cactus causes nausea, diarrhea, and temporary paralysis. Think you can tell the two apart? (See: Edible Desert Plants – Barrel Cactus Fruit).
What about Prickly Pear pads we sometimes see in grocery stories or on the menu of Mexican restaurants? What you see are generally young spring pads which naturally contain less oxalic acid. Cooking leaches out the acid. In an emergency you can eat the young pads raw. And there are some spineless cultivars that naturally contain little oxalic acid which can also be eaten raw. These were developed mainly as cattle feed.
The bottom line is you really cannot get a drink from a cactus in spite of what you may have seen in old cowboy movies.