0

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.

  • I think you shouldn't restore on top of an existing DB to start with. – dezso Apr 11 at 10:29
  • Fair enough. But so why the other commands have the IF EXISTS there? That's why I thought it's possibly supported to recreate a table, without deleting it manually first. If I should never restore on an existing DB, I wonder, why it's supported to restore single table at all? – signedav Apr 23 at 5:29
  • You can do it in another schema, or by renaming the table (either in the dump or the DB) – dezso Apr 23 at 9:36
  • Yes, thanks. Or by deleting it as well. Still in a dump are commands to drop defaults, delete table etc. that would all not be needed, if one would restore only where the table does not exist yet... – signedav Apr 24 at 10:12
  • Well, you explicitly asked pg_dump to do that when adding teh --clean option to the command line... – dezso Apr 24 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.