That sounds massively expensive. Being very generous and assuming that the piston is the length of the hole and can rise out completely, then the energy storage is still only a few times that required to pump all the water out of the hole. It doesn't start to compare to the volume behind a hydro dam (which may then have a large vertical drop to the generator further down-river), but is still much more complex.

Another proposed idea for places that have deep seas or lakes is pumping air down into a storage at the bottom of the water. This storage can even being a flexible plastic - there isn't any high loading on it because the pressure balances out. The issue with that tech is that it's not that efficient, as compressing the air going down generates a lot of heat - some of that could potentially be recovered, but there's a trade-off with simplicity. Also if the containment fails then a lot of air bubbles to the surface, potentially sinking any ship on the surface at the time.