PostGIS subquery error when trying to randomly distribute points in polygons

Question

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
  )
  ;

Answer 1

Technically, @RainForest's solution directly adresses the error you are seeing; you are SETing a single column so the respective expression (here the sub-query) must return a single value (here the result set of the sub-query).

---

To improve on the overall structure:

UPDATE
  public.unjoined_pts2 AS pt
SET
  geom = ST_GeneratePoints(lsd.geom, 1)
FROM
  public.grid_concave_hull AS lsd
WHERE
  ST_Contains(lsd.geom, pt.geom)
  AND
  EXISTS (
    SELECT
      1
    FROM
      public.unjoined_pts2 AS _pt
    WHERE
      _pt.geom && pt.geom
      AND
      _pt.id < pt.id
  )
;

For context, the original query runs a bounded (per row) and LIMITed (K)NN search that requires a sub-query. This structure is not needed here.

Note that you may need to extract a simple POINT geometry from the (here a single-member) MULTIPOINT return type, i.e.

ST_GeometryN(ST_GeneratePoints(lsd.geom, 1), 1)

Answer 2

Just delete pt.*, from subquery

SELECT 
      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)