Copying an entire database into a django-based project using psql

This title will be a bit misleading.  I know what you are thinking...

Great this guy has a way for me to define a django model then copy in an entire database--- constraints, defaults, foreign keys and all!

Well, no, I don't have a great way to do that.  Is it possible? Probably. But I don't know how to do it.

I have some tables that I want to preserve the relationship structure. So when I add tables in my django project (models.py) then sync up to the database, I need to to disable the automatically generated primary key field, so I can insert the records into the database via the \COPY command.  Finally, I need to reinstate the serialization of the id field as a constraint.

So, first, connect to the database using the method described in a previous post.

Then create the database model in models.py. In a complex data model situation, I like to bring the tables in one at a time. That means, creating a class in models.py, then syncing to the database one table (or collection of related tables) at a time.

Let's add the States class, which is simply a listing of the United States. These will be used in conjunction with the previously created Zipcodes class:

class States(models.Model):
 abbr = models.CharField(max_length=2)
 name = models.CharField(max_length=35)
 modifieduser = models.CharField(max_length=50, default='admin')
 modifieddate = models.DateField(auto_now=True)

In the powershell window, let's sync this up to the database.

(django-jay) PS C:\Python27\home\jtarlecki\djcode\django-jay\rebates> python manage.py syncdb

Which will hopefully result in some confirmation text saying that the table has been created.

Creating tables ...
Creating table rebate_states
Installing custom SQL ...
Installing indexes ...
Installed 0 object(s) from 0 fixture(s)

Now, connect to that database with psql and drop the constraint of the id field. Basically, the idea here is to change it from a SERIAL field to a simple INTEGER field.  The de-serialization drops the field's ability to be primary key in postgreSQL.

rebates=> ALTER TABLE rebate_states ALTER COLUMN id DROP DEFAULT;

Great-- with the default dropped, now let's copy in all the data.

jaytarle_rebates=> \COPY rebate_states FROM C:\Users\Dorothy\Desktop\states.csv
WITH CSV HEADER;

Now, we change the id field back into a true primary key by setting its default as nextval sequence.  First, let's look at the syntax nicely formatted.

DROP SEQUENCE rebate_states_id_seq; 

CREATE SEQUENCE rebate_states_id_seq
MAXVALUE 2147483647;
 
ALTER TABLE rebate_states 
ALTER COLUMN id TYPE INTEGER; 

ALTER TABLE rebate_states 
ALTER COLUMN id SET DEFAULT nextval('public.rebate_states_id_seq'::text);

Now, just squish all that into one line so you can execute from the psql shell:

jaytarle_rebates=> DROP SEQUENCE rebate_states_id_seq; CREATE SEQUENCE rebate_st
ates_id_seq MAXVALUE 2147483647; ALTER TABLE rebate_states ALTER COLUMN id TYPE
INTEGER; ALTER TABLE rebate_states ALTER COLUMN id SET DEFAULT nextval('public.r
ebate_states_id_seq'::text);

From which we get the following confirmation:

DROP SEQUENCE
CREATE SEQUENCE
ALTER TABLE
ALTER TABLE 

And, voila... our records are in the database and re-synced up with our django project.

Did I copy in a whole database? Well, no. I need to do this process for every table. I know, it sucks.  Perhaps there is a better way...