Postgres / postgis - ensure all polygons are closed

Question

I am needing to write a query which finds any unclosed polygons and closes them by copying the first point and creating an additional end point.

I am able to select the invalid rows:

SELECT delivery_zone_id, polygon from delivery_zone WHERE ST_IsClosed(polygon::geometry) = FALSE;

And I am able to dump the individual points from each of the polygons:

SELECT delivery_zone_id, ST_AsText((dp).geom) FROM
    (SELECT delivery_zone_id, ST_DumpPoints(polygon::geometry) AS dp 
        FROM delivery_zone 
        WHERE ST_IsClosed(polygon::geometry) = FALSE
    ) AS coords;

And the result looks like this:

1   POINT(-96.80037 33.09812)  ## Copy this point and add it to the set
1   POINT(-96.80427 33.0956)
1   POINT(-96.80401 33.09219)
1   POINT(-96.79603 33.09222)
1   POINT(-96.79346 33.09647)
1   POINT(-96.80037 33.09857)

4   POINT(-96.80037 33.099)    ## Copy this point and add it to the set
4   POINT(-96.80427 33.0956)
4   POINT(-96.80401 33.09219)
4   POINT(-96.79603 33.09222)
4   POINT(-96.79346 33.09647)
4   POINT(-96.80037 33.09923)

This is where my sql skills are lacking. I need some help copying the first point and creating a new end point with that data. Pseudo queries are welcome - I just need to see what it might look like and I can fill in the gaps.

---

Update: Final solution

Thanks to the answer from JGH below, I was able to create the following update query. This will find any unclosed polygons and add a new end point by copying the first point.

Note: this will only work with simple "single" polygons. If you have complex outer and inner polygons, you will need to make some drastic changes to this query.

UPDATE delivery_zone dz
SET polygon=ST_MakePolygon(ST_AddPoint(subquery.openline, ST_PointN(subquery.openline, 1), -1))
FROM (
  SELECT delivery_zone_id, ST_ExteriorRing(polygon::geometry) AS openline 
  FROM delivery_zone WHERE ST_IsClosed(polygon::geometry) = FALSE
) AS subquery
WHERE dz.delivery_zone_id = subquery.delivery_zone_id;

Answer 1

You can try using lines to add the point, then converting to polygon.

Let's note that creating a not-closed polygon is not possible... not too sure how you got one, and hopefully you would be able to convert them to a line at first.

So, the idea is to get the line, then add a point to it at the last position (-1). This point would be the same as the first point of this line (position 1). At last you can convert to a polygon

WITH src AS (
    SELECT ST_GeomFromText('LINESTRING(0 0, 0 1, 1 1, 1 0)') As openline)
SELECT st_asText(openline), 
        st_asText(ST_MakePolygon(st_addPoint(openline,st_PointN(openline,1),-1)))
FROM src;



      st_astext          |           st_astext
-----------------------------+--------------------------------
 LINESTRING(0 0,0 1,1 1,1 0) | POLYGON((0 0,0 1,1 1,1 0,0 0))
(1 row)