Do nothing in a trigger procedure

Question

I got a trouble when a try to execute a trigger. Let's suppose we have 2 tables and I want to copy data from table A to table B but each table got a unique constraint.

create table test1 (
 test_name varchar);

create unique index test1_uc on test1 USING btree (test_name);

create table test2 (
test_name2 varchar);

 create unique index test2_uc on test2 USING btree (test_name2);

CREATE OR REPLACE FUNCTION trig_test()
  RETURNS trigger AS
$$
BEGIN
  IF pg_trigger_depth() <> 1 THEN
    RETURN NEW;
END IF;
INSERT INTO test2(test_name2)
   VALUES(NEW.test_name2)
ON CONFLICT (test_name2) DO NOTHING;

RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_test
AFTER INSERT
ON test2
FOR EACH ROW
EXECUTE PROCEDURE trig_test();

insert into test2 values ('test');
insert into test2 values ('test'); //should do nothing ?

But I get this error:

ERROR:  duplicate key value violates unique constraint "test2_uc"
DETAIL:  Key (test_name2)=(test) already exists.

What's wrong with the trigger?

Answer 1

Your example is broken. Source and target are the same in your INSERT in the trigger, which is bound to raise a unique violation every time (except when inserting NULL) - suppressed by ON CONFLICT (test_name2) DO NOTHING, so nothing ever happens in the trigger.

You also forget about the unique constraint in your original INSERT. See below.

INSERT INTO test2(test_name2)
   VALUES(NEW.test_name2)

...

CREATE TRIGGER trigger_test
AFTER INSERT
ON test2

Start with a less confusing setup:

CREATE TABLE test1 (col1 text UNIQUE);
CREATE TABLE test2 (col2 text UNIQUE);

And it's more efficient to move pg_trigger_depth() to the trigger itself. So this would work, copying rows inserted into test1 to test2 (and not the other way), only for the first level of trigger depth:

CREATE OR REPLACE FUNCTION trig_test()
  RETURNS trigger AS
$func$
BEGIN
   INSERT INTO test2(col2)             -- !!
   VALUES (NEW.col1)                   -- !!
   ON     CONFLICT (col2) DO NOTHING;  -- !!

   RETURN NULL;
END
$func$ LANGUAGE plpgsql;

I kept it as AFTER trigger. Can be a BEFORE trigger as well, but there you'd need RETURN NEW;.

CREATE TRIGGER trigger_test
AFTER INSERT ON test1                  -- !!
FOR EACH ROW 
WHEN (pg_trigger_depth() < 1)          -- !!
EXECUTE PROCEDURE trig_test();

Why (pg_trigger_depth() < 1)?

• How to prevent a PostgreSQL trigger from being fired by another trigger?

Note that you trap unique violations in test2 this way (nothing happens), but unique violations in test1 would still raise an exception unless you have ON CONFLICT ... DO NOTHING there as well. Your test is wishful thinking:

insert into test2 values ('test'); //should do nothing ?

Has to be:

INSERT INTO test1 values ('test') ON CONFLICT (col1) DO NOTHING;

Alternative: Chain two INSERT with a CTE

If you have control over INSERT commands on test1, you can do this instead of the trigger:

WITH ins1 AS (
   INSERT INTO test1(col1)
   VALUES ('foo')                  -- your value goes here
   ON CONFLICT (col1) DO NOTHING
   RETURNING *
   )
INSERT INTO test2(col2)
SELECT col1 FROM ins1
ON CONFLICT (col2) DO NOTHING;

Related:

• Insert data in 3 tables at a time using Postgres
• PostgreSQL multi INSERT...RETURNING with multiple columns