postgresql pg_dump without schema name

Question

pg_dump version 10.3 started adding the schema name explicitly to the table name. So what used to be

INSERT INTO table (id) VALUES (1);

Is now

INSERT INTO public.table (id) VALUES (1);

How can you switch it off? My problem is, that I dump this into a mariadb where you don't have schema names.

Answer 1

If it's just the INSERT statements that you care about, like me, then I believe this is safe, and will remove the "public" schema from your tables.

sed -i -e 's/^INSERT INTO public\./INSERT INTO /' <filename.sql>

It modifies the file in place, so be careful.