Non-primary auto-incrementing field with Postgres

[Thomas Hauk]

I am working on a project that uses Django 1.10.5 with Postgres 9.6 (and Python 3.6.1).

 

I am currently migrating historical data from an old system into the new system. This historical data has a table with a (non-primary key) “ID” column. I would like to migrate these rows into the new database, and have that column be auto-incrementing (starting from, say, 100000) for new rows inserted.

 

I think the way to do this with raw Postgres SQL would be to create a serial field, and then run a SQL command like “SELECT SETVAL('some_table_field_id_seq', 100000)”. I think.  :)

 

Does Django expose functionality that lets me accomplish this?

 

I looked at AutoField, but it doesn’t allow usage when primary_key=False.

 

T

 

[Scot Hacker]

One strategy might be to:

1) Bring in the data under a different column name ('old_id' ?)

2) In a single migration, drop the default ID column,  rename old_id to id, and give it primary_key=True

That should preserve your old IDs and set the next auto_increment to the next highest value (and if it doesn't, you can set the next val manually immediately afterwards).

./s

[Melvyn Sopacua]

On Monday 19 June 2017 16:29:48 Thomas Hauk wrote:

> I am working on a project that uses Django 1.10.5 with Postgres 9.6

> (and Python 3.6.1).

>

> I am currently migrating historical data from an old system into the

> new system. This historical data has a table with a (non-primary key)

> "ID" column. I would like to migrate these rows into the new

> database, and have that column be auto-incrementing (starting from,

> say, 100000) for new rows inserted.

>

> I think the way to do this with raw Postgres SQL would be to create a

> serial field, and then run a SQL command like "SELECT

> SETVAL('some_table_field_id_seq', 100000)". I think. :)

>

> Does Django expose functionality that lets me accomplish this?

 

First and foremost, verify if ID meets your requirements:

- All values are unique

- They are integers

- Their values are smaller then your new cut-off (100000)

 

Then you can simply transform the field explicitly to primary key and then to BigAutoField. Setting the next value can be done using RunSQL.

 

--

Melvyn Sopacua

[th...@copperleaf.com]

On Tuesday, June 20, 2017 at 10:40:05 PM UTC-7, Scot Hacker wrote:

One strategy might be to:

1) Bring in the data under a different column name ('old_id' ?)

2) In a single migration, drop the default ID column,  rename old_id to id, and give it primary_key=True

I can't change the primary key for this table, unfortunately.

T