INSERT INTO geotable (geometrydata) values (ST_GeomFromGeoJSON(@geometrydata));

How can I run the above insert statement to posgresql database using PostgreSQLCopyHelper in c# code. Tried to do something like this.

var copyHelper = new PostgreSQLCopyHelper<GeoData>("geotable ") .Map("geometrydata", x => x.geometrydata, NpgsqlDbType.Geometry);

geometrydata is a string like below.

`var geometrydata = "{\"type\":\"Polygon\",\"coordinates\":[[[-81.800007507357,24.491670214271,0.0],[-81.800007507357,24.500003548802,0.0],[-81.7916741718259,24.500003548802,0.0],[-81.7916741718259,24.491670214271,0.0],[-81.800007507357,24.491670214271,0.0]]]}"`

How can I format data using postgis function when using PostgreSQLCopyHelper. (https://github.com/PostgreSQLCopyHelper/PostgreSQLCopyHelper )

CodePudding user response：

You cannot call functions while loading data with COPY.

If you really need that, you would have to create a view on the table and use an INSTEAD OF trigger on the view that inserts a suitable processed row into the table.

It will probably perform better to load the data to a staging table and perform the conversion with an UPDATE on all rows.