I have a table containing point geometries and a buffer_distance column. I want to create a buffer around each point using the buffer_distance column and check if any point is within each buffer but making sure to ignore the point at the center of each buffer polygon (the point from which I created the buffer).
I have the following query but it returns True for all values in the point_within column as it is not ignoring the point at the center of the buffered geometry.
SELECT p.id, ST_Buffer(p.geom, p.buffer_distance), ST_Within(p.geom, ST_Buffer(p.geom, p.buffer_distance)) AS point_within
FROM point_table as p
What you're looking for is a spatial join. Join the table with itself and check which records lie inside each individual buffer, and then exclude the duplicates:
SELECT * FROM point_table p
JOIN point_table p2 ON
ST_Contains(ST_Buffer(p.geom, p.buffer_distance), p2.geom) AND
p2.id <> p.id;
Demo: db<>fiddle