Triggers (for generated code) produce error when doing restore data on postgresql

Question

I had backup my data on postgresql db into 'new_backup_data.sql' using pg_dump --column-inserts -h localhost -U postgres micro -a -F p > /tmp/new_backup_data.sql, then I want to restore the data on different database which is already have same schema tables,trigger. psql -U postgres micro < /tmp/new_backup_data.sql

But the trigger always make it fail. Here is the error:

QUERY:  SELECT                                  LPAD(cast(cast(substring(registration_code, 10) AS integer) + 1 AS varchar), 4, '0')
    FROM t_registration
    ORDER BY id
        DESC LIMIT 1
CONTEXT:  PL/pgSQL function public.generate_registration_code() line 7 at SQL statement
ERROR:  relation "t_registration" does not exist
LINE 2:     FROM t_registration
                 ^
QUERY:  SELECT                                  LPAD(cast(cast(substring(registration_code, 10) AS integer) + 1 AS varchar), 4, '0')
    FROM t_registration
    ORDER BY id
        DESC LIMIT 1
CONTEXT:  PL/pgSQL function public.generate_registration_code() line 7 at SQL statement
ERROR:  relation "t_registration" does not exist
LINE 2:     FROM t_registration
                 ^
QUERY:  SELECT                                  LPAD(cast(cast(substring(registration_code, 10) AS integer) + 1 AS varchar), 4, '0')
    FROM t_registration
    ORDER BY id
        DESC LIMIT 1
CONTEXT:  PL/pgSQL function public.generate_registration_code() line 7 at SQL statement
ERROR:  relation "t_registration" does not exist
LINE 2:     FROM t_registration

Here is some my query when tried to open the backup sql file:

INSERT INTO public.t_registration (id, created_by, created_date, last_modified_by, last_modified_date, is_active, registration_code, registration_type_code, app_id, app_code, account_code) VALUES (94, 'Admin', '2020-07-16 09:04:32.095', 'Admin', '2020-07-16 09:04:32.095', true, 'REG2007160001', 'R2007001', 1, 'APP202007001', NULL);

When I run the query manually via PG admin it works and no problem with the trigger, but why when i restore it always produce those errors?

Note : My trigger is used for generate pattern of unique code

Answer 1

Look at the top of the dump file. I'm guessing you have something like SELECT pg_catalog.set_config('search_path', '', false); This blanks the search_path. Since the table name in the function is not schema qualified it is not found. The pgAdmin case works as the search_path is set there. FYI, the dump file is using COPY to pull in the data. That still fires the triggers though.

UPDATE. To fix schema qualify the table name in the function.