Postgresql setting next id to write to

Question

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?

Answer 1

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.