The following is some code from @geozelot that I'm repurposing to randomly distribute duplicate points inside the polygon they are contained within, however, I get the following subquery error:
ERROR: subquery must return only one column LINE 207: geom = ( ^
UPDATE
public.unjoined_pts2 AS pt
SET
geom = (
SELECT
pt.*,
ST_GeneratePoints(lsd.geom, 1, floor(random() * 5000 + 1)::int)
FROM
public.unjoined_pts2 AS pt,
public.grid_concave_hull AS lsd
WHERE
ST_Contains(lsd.geom, pt.geom)
)
WHERE
EXISTS (
SELECT
1
FROM
public.unjoined_pts2 AS _pt
WHERE
-- bbox of a point is the point; alternative to ST_Equals for points
_pt.geom && pt.geom
AND
-- update all duplicates but the one with the lowest <id>
_pt.id < pt.id
)
;