You can use ST_Point_Inside_Circle function. The function is self explainained :
boolean ST_Point_Inside_Circle(geometry a_point, float center_x, float center_y, float radius);
The syntax for this functions is
Returns the true if the geometry is a point and is inside the circle.
Returns false otherwise.
Here's an example:
select * from TABLE as A where ST_Point_Inside_Circle(a.geom,x,y,R);
-- where the x,y is the center of your circle thus C(x,y). R is the Radius.
--The SRID must be in something that uses meters.
I haven't seen the actual function yet, but I have a hunch that it creates a buffer zone for each point and checks if it's inside or not, and returns T/F accordingly.
Another way is with a subquery: You create in your SQ your circle, and then check each point of yours if it is inside or not.
with MyCircle as (select ST_Buffer(ST_SetSRID(ST_MakePoint(x, y),SRID, R) as geom)
-- Circle centered at C(x,y), Radius R.
-- Use the same srid as your points in your table.
select * from TABLE as A where st_intersects(MyCircle.geom,a.geom);