trigger to save data when fails INSERT on another table?

Question

I've been googling for a while and found no answer for this question.

Consider this, I have a table test_table(id serial, name varchar(15), surname varchar(40)). If someone tries to insert a value higher than 15 characters in the column name, it will return permission denied.

What I need to know is if there is a way to create a trigger that anytime it fails to insert on that test_table, it saves (inserts) all the "lost" data in another table (maybe like) log_failed_inserts(failedid int, failedname text, failedsurname text).

Please refrain answering stuff like "just increase the column limit" because that was just an example. I need to know if there is a way to log the data on failed INSERT commands.

Edit: Just to be clearer as the above sentence wasn't enough. No matter what error occurs when inserting, being 'invalid data type', 'length too big', 'you do not have permission to insert' (maybe not this last one), I want to log that something/someone tried to insert the values X, Y, Z on the table test_table and it failed.

Answer 1

The best solution here is to write a function that catches any errors during INSERT and performs the alternative instead:

CREATE OR REPLACE FUNCTION do_insert(p_name text, p_surname text) RETURNS void
   LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO testtable (name, surname)
      VALUES (p_name, p_surname);
EXCEPTION
   WHEN OTHERS THEN
      INSERT INTO log_failed_inserts(failedname, failedsurname)
         VALUES (p_name, p_surname);
END;$$;

If you don't want a function, you could use a DO statement:

DO
$$BEGIN
   INSERT ...
EXCEPTION
   WHEN OTHERS THEN
      INSERT ...
END;$$;

That has the disadvantage that you cannot pass parameters to it.

The third solutions is to explicitly use savepoints in SQL:

BEGIN;
SAVEPOINT sp1;
INSERT INTO test_table ...;

If there is no error, proceed with

COMMIT;

If there is an error:

ROLLBACK TO SAVEPOINT sp1;
INSERT INTO log_failed_inserts ...;
COMMIT;