On PostgreSQL 9.6, how can I do the equivalent of CREATE TRIGGER IF NOT EXISTS? I noticed that in PostgreSQL 14 you can do CREATE OR REPLACE TRIGGER but I'm using 9.6 which doesn't have that.
1 Answer
Yo can simply run the CREATE TRIGGER statement and ignore the error:
DO
$$BEGIN
CREATE TRIGGER ...
EXCEPTION
WHEN duplicate_object THEN
NULL;
END;$$;
If CREATE OR REPLACE TRIGGER is a solution for you, that's yet another argument for an upgrade. Note that you are running an unsupported version of PostgreSQL, and that's not a smart thing to do if your data are important to you. I can tell from my experience that "it has worked so far, so let's not change anything" is not a good recipe to avoid trouble.
-
Even upgrading to PostgreSQL 14 so I can do
CREATE OR REPLACE TRIGGERisn't ideal, because it's still not the same asCREATE TRIGGER IF NOT EXISTS. It's still a second-rate solution, because I really don't want to replace the trigger.Starscream512– Starscream5122023-07-12 09:05:17 +00:00Commented Jul 12, 2023 at 9:05 -
Well, I gave you the solution.Laurenz Albe– Laurenz Albe2023-07-12 09:29:59 +00:00Commented Jul 12, 2023 at 9:29
-
I'll accept your posted solution as the correct one. The comment about upgrading was actually the "second-rate" solution.Starscream512– Starscream5122023-07-12 09:53:32 +00:00Commented Jul 12, 2023 at 9:53
-
True. I have reworded the answer. There are no good reasons for running an old version of PostgreSQL.Laurenz Albe– Laurenz Albe2023-07-12 10:05:09 +00:00Commented Jul 12, 2023 at 10:05
CREATE TRIGGER IF NOT EXISTSif that is possible, but will settle forCREATE OR REPLACE TRIGGERbut even that isn't possible. Replacing the trigger is fine, because the new one is exactly the same as the old one.