'postgresql pg_dump without schema name
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.
Solution 1:[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.
Solution 2:[2]
I also searched this switcher and it's not exists. This behavior was added because of this - https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path
In my work I now use new variant of dump with renaming schemas in transaction.
Solution 3:[3]
If you have Docker installed you can do a trick:
docker run -it postgres:10.2 pg_dump postgresql://user:pass@host/database
Solution 4:[4]
Of course it's just linux decision (not postgres), but it helps you i suppose
sed -i 's/public.//g' your_dmp_file
P.s. I didn't check this, may be you should fix it
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Karl von Randow |
| Solution 2 | Tatiana P |
| Solution 3 | pensnarik |
| Solution 4 |