CODEWITHSUNDEEP
mysqldjangopostgresqlpostgresql-9.0
demux
demux

Reputation: 4654

Postgresql setting next id to write to

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?

Upvotes: 5

Views: 1300

Answers (2)

Ketema
Ketema

Reputation: 6578

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.

Upvotes: 2

Jonathan Hall
Jonathan Hall

Reputation: 79674

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.

Upvotes: 7

Related Questions