I am trying to load the local postgres database with json dumped data(from online db) by using the loaddata command. But it fails with "Integrity Error" as I already had some data in the db with the primary key. Then I tried to flush the database so as to take it to state just after syncdb. But it gives me the following message and fails.
You have requested a flush of the database.
This will IRREVERSIBLY DESTROY all data currently in the 'app' database,
and return each table to the state it was in after syncdb.
Are you sure you want to do this?
Type 'yes' to continue, or 'no' to cancel: yes
Error: Database app couldn't be flushed. Possible reasons:
* The database isn't running or isn't configured correctly.
* At least one of the expected database tables doesn't exist.
* The SQL was invalid.
Hint: Look at the output of 'django-admin.py sqlflush'. That's the SQL this command wasn't able to run.
The full error: cannot truncate a table referenced in a foreign key constraint
DETAIL: Table "taskapp_taskrequest" references "taskapp_task".
HINT: Truncate table "taskapp_taskrequest" at the same time, or use TRUNCATE ... CASCADE.
This is the corresponding output for sqlflush command
BEGIN;
TRUNCATE "auth_permission", "auth_group", "tagging_taggeditem", "auth_user_user_permissions", "taskapp_task_reviewers", "taskapp_task", "django_site", "profile_profile", "django_content_type", "profile_notification", "django_session", "auth_user_groups", "taskapp_pyntrequest", "profile_rolerequest", "tagging_tag", "taskapp_textbook_chapters", "registration_registrationprofile", "taskapp_textbook", "django_admin_log", "auth_group_permissions", "taskapp_task_selected_users", "taskapp_taskcomment", "south_migrationhistory", "taskapp_task_claimed_users", "taskapp_taskclaim", "taskapp_workreport", "auth_message", "taskapp_reportcomment", "auth_user";
SELECT setval(pg_get_serial_sequence('"registration_registrationprofile"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"tagging_tag"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"tagging_taggeditem"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"south_migrationhistory"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_permission"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_group"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_user"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"auth_message"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"django_content_type"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"django_site"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"django_admin_log"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"profile_profile"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"profile_notification"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"profile_rolerequest"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_task"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_taskcomment"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_taskclaim"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_workreport"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_reportcomment"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_pyntrequest"','id'), 1, false);
SELECT setval(pg_get_serial_sequence('"taskapp_textbook"','id'), 1, false);
COMMIT;
I have a database with multiple models and many foreign key relations among them. From what I read over the internet, I understand that Truncate should used to drop tables which are dependent on. I can't quite figure the exact syntax for using it in the dbshell.
I also accessing the postgresql shell as the sudo user and tried to drop the database using
DROP DATABASE DBNAME
command. But the database persists.
Edit:
Thanks to stevejalim's comment I was able to drop the database and created a new one. I ran syncdb command again and created the db again. But trying to load the db using loaddata command throws the error
IntegrityError: duplicate key value violates unique constraint
Any help on this would be much appreciated.
DROP DATABASE DBNAME;
– stevejalim Jan 31 '12 at 15:25truncate
does not drop any tables. It only deletes all rows. – a_horse_with_no_name Jan 31 '12 at 16:27