"create or replace trigger" not available on AWS RDS/Postgres-CodePudding

I have the following:

CREATE OR REPLACE TRIGGER trigger_commands
    AFTER INSERT ON commands
    FOR EACH ROW EXECUTE PROCEDURE on_commands_change();

it works on a local Postgres instance, but fails on AWS with:

[42601] ERROR: syntax error at or near "TRIGGER"

however, if I remove the 'OR REPLACE' part:

CREATE TRIGGER trigger_commands
    AFTER INSERT ON commands
    FOR EACH ROW EXECUTE PROCEDURE on_commands_change();

then RDS accepts it. Why is that? and what would be the best way to update the trigger at every code restart?

CodePudding user response：

It's most likely a Postgresql version mismatch, CREATE OR REPLACE TRIGGER works only on Postgresql 14 (see documentation, 14 and 13).

I'd suggest to use DROP TRIGGER IF EXISTS then CREATE TRIGGER for backwards compatibility.