Handle pg_error on generated columns-CodePudding

I have a table that consists of some PostGIS related data. These data are generated automatically on INSERT or UPDATE.

ALTER TABLE "Room" ADD COLUMN geolocation geography(Polygon, 4326) GENERATED ALWAYS AS (ST_MakePolygon("polygon"::geometry)) STORED;

Sometimes the data provided on the polygon column might not fit the generation function and cause an error. I wanted to handle this error and set a default value when it fail.

-- Last resort options --

Creating postgres functions that handle this task but that would disassociate the work from the codebase.

CodePudding user response：

As you're using ST_MakePolygon I assume that the column "polygon" contains LINESTRINGs and sometimes they are invalid, which is raising an error in your DDL statement.

What you could do is to create a CASE conditional that validates the geometry before trying to create the polygon. For instance, this query checks if the geometry is valid, if it is a LINESTRING and if its ring is closed before attempting to create the POLYGON. In case it fails, it generates an empty polygon (change it to NULL if you want):

ALTER TABLE "Room" 
  ADD COLUMN geolocation geography(Polygon, 4326) 
  GENERATED ALWAYS AS (
    CASE 
      WHEN ST_IsValid("polygon") AND 
           GeometryType("polygon")='LINESTRING' AND
           ST_IsClosed("polygon"::geometry) THEN   
        ST_MakePolygon("polygon"::geometry)
      ELSE 
        'POLYGON EMPTY' 
      END) STORED;

Demo: db<>fiddle