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?

You can set the type of the column to serial and postgreSQL will create a sequence for you automatically. — Johan
– Johan, Commented Nov 3, 2011 at 10:38
I'm almost certain that it is, since the table is created by django. — demux
– demux, Commented Nov 3, 2011 at 10:46
I found the answer here: stackoverflow.com/questions/1709705/… — demux
– demux, Commented Nov 3, 2011 at 11:47

Jonathan Hall · Accepted Answer · 2011-11-03 10:46:50Z

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.

answered Nov 3, 2011 at 10:46

Jonathan Hall

80.5k19 gold badges162 silver badges206 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

demux Over a year ago

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)

Ketema · Accepted Answer · 2011-11-03 10:59:21Z

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.

answered Nov 3, 2011 at 10:59

Ketema

6,7883 gold badges23 silver badges24 bronze badges

2 Comments

demux Over a year ago

I found it here: postgresql.org/docs/current/static/functions-sequence.html I'm new to postgresql; can you show an example?

Ketema Over a year ago

SELECT setval('nameOfSequence', 42);

Collectives™ on Stack Overflow

Postgresql setting next id to write to

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related