Backup table does not replicate geometry, fed by trigger in pgAdmin (postGIS)

Question

I have two tables, A and B. B is a backup of A.

Table A has a trigger:

CREATE OR REPLACE TRIGGER trigger
    AFTER INSERT OR UPDATE OR DELETE
    ON A
    FOR EACH ROW
    EXECUTE FUNCTION trigger_function();

And the trigger function:

INSERT INTO B ( 
        id,
        geom
)
VALUES (
    OLD.id,
    OLD.geom
);
    return null;

Tables A and B have full rights, both have a primary key (id for A and B has its own).

My idea is that this will fill table B with the points made/changed in table A, and those points can be kept as a backup. This works for for all fields I try except geom, for some reason geom can't be written to B by the trigger. I have populated B with points without issue through FME and can work with them fine in QGIS, so the issue (I assume) must be with my trigger setup or pgAdmin.

Any clues as to why geom can't be transferred?

Answer 1

Row-level triggers gain access to the transitional state of each respective row via the trigger-level variables OLD & NEW - however, the state of these variables are dependent on the trigger operation:

• ON INSERT/UPDATE triggers have access to values in NEW
• ON DELETE/UPDATE triggers have access to values in OLD

I.e. for INSERT operations the variable OLD does not hold any values other than NULL!

---

The respective PostgreSQL docs have a comprehensive example for an audit table trigger, showing the conditional access to these variables based on the operation.

Here tailored to your example:

CREATE OR REPLACE FUNCTION trigger_function()
  RETURNS TRIGGER
  LANGUAGE 'plpgsql' AS
  $$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      INSERT INTO B ( id, geom ) VALUES ( NEW.id, NEW.geom );
    ELSE
      INSERT INTO B ( id, geom ) VALUES ( OLD.id, OLD.geom );
    END IF;

    RETURN NULL;
  END;
  $$
;

Since these variables will be NULL if not supported by the operation, your example could be simplified to

CREATE OR REPLACE FUNCTION trigger_function()
  RETURNS TRIGGER
  LANGUAGE 'plpgsql' AS
  $$
  BEGIN
    INSERT INTO B ( id, geom ) VALUES
      ( COALESCE(OLD.id, NEW.id), COALESCE(OLD.geom, NEW.geom) )
    ;

    RETURN NULL;
  END;
  $$
;