What many players would say is that, for defending cities, offensive units are often better.
Say, an enemy stack of a Spearman and 3 Archers is entering your territory, heading for one of your cities. You're better off, if you've got some offensive units around (and better, some catapults to take some hitpoints off the Spearman first!) to attack this stack on your own turn. That's better than sitting in your cities with Spearmen, waiting to be attacked.
What a hostile AI also often does is entering your territory with solitary Spear- or Pikemen, heading for your luxuries or strategic resources in an attempt to pillage those. You need offensive units to attack those nasty AI invaders.
Defensive units are not useless, but offensive units offer you more tactical options.