PostGIS update trigger

I have table_1 having column point_id and geometry (Point) , table_2 has got columns: area_id, geometry ( Polygon) and a table_3 which contains list of all the point_id from the table_1 and area_id from table_2 , if point within the polygon.

I am trying to update table_3 every time new polygon is added to table_2, so the point_id and area_id are populated for every point from table_1 which is within new polygon from table_2

CREATE OR REPLACE FUNCTION locations_update()
RETURNS TRIGGER AS $func$
DECLARE
BEGIN 
  insert into table_3 (point_id,area_id)
    select
        table_1.point_id , new.table_2.area_id
    from
        table_1  
    join table_2  
on
    ST_WITHIN(table_1.geometry ,    new.table_2.geometry) ;
   RETURN new;
END;$func$
language plpgsql;



CREATE TRIGGER locations_update_tr AFTER INSERT ON table_2
FOR EACH ROW EXECUTE PROCEDURE locations_update();

What updates to the code above needed to avoid unique constraint error

ERROR: duplicate key value violates unique constraint 
DETAIL: Key (point_id, area_id)=(1, 1) already exists.

Thanks

Solution

You are joining the two tables, but the join condition is only considering the 1st table so it ends up being a cross-join.

In fact, you don't need to join at all since all the required information is in the NEW object.

SELECT
   table_1.point_id, NEW.table_2.area_id
FROM
    table_1  
WHERE
   ST_WITHIN(table_1.geometry, NEW.table_2.geometry) ;

PS: and of course you have another trigger to remove entry from table 3 when one is deleted in table 1 or 2.