5

I recently migrated a database from mysql to pgsql 9.

But now when I try to create a new object (in django admin) it tells me that the id I'm trying to use (started at one and has increased each time I tried) is already used.

I'm guessing that there is a pointer or index which needs to be set to the last used id. Am I correct?

demux
  • 4,544
  • 2
  • 32
  • 56
  • You can set the type of the column to `serial` and postgreSQL will create a sequence for you automatically. – Johan Nov 03 '11 at 10:38
  • I'm almost certain that it is, since the table is created by django. – demux Nov 03 '11 at 10:46
  • I found the answer here: http://stackoverflow.com/questions/1709705/postgresql-nextval-generating-existing-values – demux Nov 03 '11 at 11:47

2 Answers2

7

When you define your table, the PostgreSQL equivalent to 'auto_increment' is:

CREATE TABLE foo (
    id    SERIAL,
    ...
);

If your table is already created (as I suspect it is), you can add this manually:

CREATE SEQUENCE foo_id_seq;
ALTER TABLE foo ALTER COLUMN id SET DEFAULT nextval('foo_id_seq');

Note that if you want to stick with the default name that Pg would have given you use the following format for your sequence name:

<table name>_<column name>_seq

Thus in my example, foo_id_seq.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
  • There was already a table and a sequence created by django. So I did not have to create the sequence and alter the table. But what worked was this: select setval('uploads_id_seq', 6965); (uploads being the table name) – demux Feb 02 '12 at 19:13
2

If the table was migrated and it uses serial to replace the mysql auto increment column, your data was probably migrated without incrementing the serial sequence. Look up the postgresql setval function to set your sequence to a value above the highest existing key in your table.

Ketema
  • 6,108
  • 3
  • 21
  • 24