They are so inaccurate that they seem useless. At the ranges you have a pistol be viable you might as well use a bayonet or melee weapon. What’s the point for anything other than prestige and dueling?
For civilians, pistols were bought for defense. Often enough they were bought in pairs, two large ones stored in a case - yes, just like the case you see being opened in the dueling scene in those films, but very few people bought pistols for the purpose of dueling. Are you a silversmith, and a target for armed robbery? Perhaps you'll have a pair of pistols in the house. For travelers, carrying valuables, there were others that were small and concealable: screw-barrel pistols that could drop into a pocket.
For the military, the cavalry often used pistols, carried in a pair of bottle-shaped holsters slung in front of the saddle. Pistols, because it was immensely difficult for a mounted soldier to reload a muzzle-loading gun. Short carbines and musketoons were also used- often attached to a sling swivel on bandolier, so they would be harder to drop- but they were still very awkward. It's for this reason that the cavalry had priority for getting breechloading guns in the US Civil War , and why revolvers would be popular among cavalrymen.
And yes, unlike what those first person shooter games would have you believe, a pistol is indeed a close-range weapon . But think of how many of these engagements would be close-range. Having two pistols gave a mounted soldier two shots he could make at the end of a charge. And confrontations with a housebreaker or highwayman would not be at a long distance, either. Whether a homeowner would be better off defending with a fowler ( shotgun) or musket instead of a pair of pistols ( or just investing in better locks and a dog) of course is a debate that is still going on....
The black powder pistol began as a cavalry weapon. Before the pistol, the main weapon of cavalry was often (a) the lance, (b) the bow, or (c) the javelin. Reach or range is an important factor for each of these weapons. Why do cavalry care about range/reach? It's very important when fighting infantry-in-formation - such infantry usually use spears, either one-handed spears with shield, or two-handed spears (e.g., pikes), or other polearms. If cavalry try to engage in close combat with infantry in an intact formation, they are usually outnumbered, and it can be very difficult for them (having better armour than the infantry can help a lot). Bows and javelins let cavalry attack infantry while staying out of reach of the infantry weapons, and the lance can also allow this, depending on the weapons used by the infantry.
If the lance doesn't provide more reach than the spears used by the infantry, e.g., when fighting pikemen, armour for both rider and horse becomes very important. However, it is not always enough, especially when the infantry are (partly) equipped with guns that can penetrate that armour. For example, at the Battle of Ceresole (1544):
the French armoured lancers suffered badly against the enemy pike and shot. On both flanks, the French cavalry first defeated the enemy cavalry, and proceeded to attack the infantry (predominantly pikemen). On the right, they attacked and failed. On the left, they attacked and failed repeatedly, and suffered very heavy losses (over 3/4 of their strength). In the centre, they had the opportunity to attack engaged enemy infantry in the flank, which won the battle.
Therefore, a cavalry weapon that could be used against infantry from outside the reach of a pike was desirable. Thus, the pistol. While the pistol was less accurate than an arquebus or musket, it was still accurate enough to 30m or more, and was therefore effective from outside the reach of a pike. While breastplate that were designed to resist musket-balls would stop pistol shot with ease, a pistol could be effective against lighter armour, and for pistol-armed cavalry was generally their best anti-armour weapon.
The dominance of the pike as an infantry weapon helped drive the adoption of the pistol as a major cavalry weapon, replacing the lance. As the gun replaced the pike as the most common infantry weapon, followed by the pike disappearing in favour of the infantryman with musket and bayonet combining both gun and spear in one, the lance became feasible as a weapon again, and reappeared in many armies (and was still in use in WWI, sometimes with success). An alternative cavalry weapon was the carbine, a short long gun, easier to load on horseback than a musket (but not as easy to load as a pistol). Compared to a pistol, a carbine had better long-range performance. Compared to a carbine, it was easier to carry multiple pistols, and get multiple shots before needing to reload.
The pistol was also attractive to others. For example, it was useful in boarding battles on ships, outranging boarding pikes and swords, and having very good stopping power.
Finally, we can note three more points about cavalry weapons:
It was possible to carry both lance and pistol, and when the lance came back into use after the pike disappeared, lancers often carried both.
The revolver greatly increased the firepower of cavalry - a pair of revolvers meant that cavalry had 12 shots to use without reloading, compared to 2 with a pair of pistols, or 1 with a muzzle-loading carbine.
In many parts of Asia, cavalry continued to the bow as their solution to reloading on horseback. Asian cavalry sometimes carried both musket and bow, using the musket for a powerful first shot, and the bow for repeated shooting. Asian lancers would often carry a bow as well (and sometimes a musket in addition to their bow).