Common or garden foam isn't much good on its own.
Air is what does the insulation, your weight squeezes all the air out when you lie on it, so you need it to be quite thick for warmth as well as comfort, it's (very) bulky to carry, and it soaks up water.
The generally used options are:
Closed cell foam
In this, the bubbles in the foam don't connect with each other. Since your weight doesn't squeeze out the air, it insulates well, and you need less thickness for comfort. It's still bulky to carry, so it's normally limited to 9-12 mm thick, which takes the edge off the ground but isn't as comfortable as inflatable mats.
Light and relatively cheap, fairly indestrucible
Self inflating mats
Self-inflating mats are normal foam with a nylon cover bonded to the outside to keep the air in. Comfort is fairly good, depending on thickness. Light versions are about 1 inch thick, which is OK for back or front sleepers, but side sleepers may find a hip touching the ground. Thicker versions are available, and give good comfort, but are quite a lot bulkier to carry than the thinner versions, which fold in half before being rolled. Light mats also often have the foam core perforated for lightness, which reduces the insulation enough that they can feel cold in winter.
Can puncture, and can delaminate, with the cover separating from the foam core to give a big bubble. Punctures can be patched, but delamination requires replacement.
Lightweight air beds
Just like a lightweight version of a lilo. Comfort is good, insulation is poor, so they are summer only.
Can puncture (and be patched), or the baffles separating the separate "tubes" can fail, which generally renders the mat unusable.
Insulated air beds.
The same as above, but with internal insulation in the form of down, synthetic fibre fill, or multiple baffles to prevent air movement. In the case of down, an additional failure mode is down leaking out through the valve.
Usually relatively expensive.
Insulation varies from "not winter" to "arctic". Insulation values are usually given as "R-values", which may be in archaic US units (BTUs, square feet, deg F, hours), or SI units (Joules, sq m, Deg K, seconds). The conversion factor is 5.7, with US units coming in the 2 to 7 range, and SI units in the 0.5 to 1.3 range.
I find that about 2.5 (US) is usable in zero degree weather, but is noticeably cool, but not so cool I don't sleep.
The main brands are Thermarest (Closed cell foam, self-inflating, or multiple baffle insulated air beds), and Exped (down or synthetic insulated air beds). Both provide good warranty replacement schemes in case of failure, but that's not much good until you get home. There are other relatively new brand that don't yet have a lot of user feedback (eg Sea to Summit), and many cheaper versions, most commonly the self-inflating mats.
FWIW, I use Thermarest Neoair (baffled insulated air mat)
My short original (now Xlite) is marginal for winter, but a short version rolls up to the size of a 750 ml bidon. The full length Xtherm is warm, but a fatter roll (~15 cm dia)