Add the buffer to the existing table and spatial join

Question

I have a spatial database with multiple tables, I have created a buffer for a table with points and wanted to add the buffer to the existing table. And wanted to do a spatial join like using this buffer and another table with points to count the number of points inside each buffer and add it as a new column in the existing buffer table. I cannot figure it out.

SELECT ST_Buffer(geom::geography,100) FROM public.operation;
UPDATE operations SET buffer = ST_Buffer(geom::geography,100)::geometry;

FROM "Supermarket" AS pts, "geom" as ST_Buffer
WHERE ST_Contains( the_geom, pts.location)

Answer 1

First add a new geometry column with AddGeometryColumn..

SELECT AddGeometryColumn ('public','operations','buffer',4326,'POLYGON',2);

.. and then insert the buffers in the new column with an update

UPDATE operations SET buffer = ST_Buffer(geom::geography,100)::geometry;

EDIT 1: Adding a new column to table ´operations` and filling it with the amount of points from another table that spatially overlap with the new buffers:

ALTER TABLE operations ADD COLUMN pts int;

UPDATE operations o 
SET pts = (SELECT count(*) FROM supermarket s
           WHERE ST_Contains(o.buffer,s.geom));

EDIT 2 (See comments):

CREATE INDEX idx_operation_geom ON operations USING gist (geom);
CREATE INDEX idx_supermarket_geom ON supermarket USING gist (geom);