Using triggers to populate a geometry column

In this recipe, we imagine that we have ongoing updates to our database, which needs spatial representation; however, in this case, we want a hard-coded geometry column to be updated each time an INSERT operation takes place on the database, converting our x and y values to geometry as they are inserted in the database.

The advantage of this approach is that the geometry is then registered in the geometry_columns view, and therefore this approach works reliably with more PostGIS client types than creating a geospatial view. This also provides the advantage of allowing for a spatial index that can significantly speed up a variety of queries. The disadvantage for users using PostgreSQL versions lower than Version 9.0 is that, without a WHERE clause within the trigger, every time an insert takes place, the trigger will be calculated on all points to create geometry. This method could be very expensive on large datasets. However, for users of PostgreSQL...