A dump creates a command to remove default values like:
ALTER TABLE IF EXISTS test.simulation ALTER COLUMN gid DROP DEFAULT;
When on restore the column with the default value do not exist anymore, the restore gets an error:
ERROR: column "gid" of relation "simulation" does not exist
I solved my problem by deleting the table first manually, but I wonder if there would be a better sollution.
The scenario looks like this
The table "test.simulation" before creating the dump:
thebase=# \d+ test.simulation
Table "test.simulation"
Column | Type | Modifiers | Storage | Stats target | Description
------------+--------------------------------+---------------------------------------------------------------------+---------+--------------+-------------
gid | integer | not null default nextval('test.simulation_gid_seq'::regclass) | plain | |
objectid | numeric | | main | |
I make a dump of the table like this:
pg_dump --clean --if-exists -hlocalhost -p5432 -dthebase -Upostgres -ttest.simulation -Fplain -f/var/lib/postgresql/backup.sql"
Then I remove the column "gid" from the table "test.simulation". And because I shouldn't have done it I recreate it:
psql -hlocalhost -p5432 -dthebase -Upostgres --single-transaction -v 'ON_ERROR_STOP=1' -f/var/lib/postgresql/backup.sql"
... and the mentioned ERROR occurs. Because it checks if the table exists, but it does not have something like a DROP DEFAULT IF EXISTS
option.
pg_dump
to do that when adding teh--clean
option to the command line...