Menu
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?
339
PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.
By simply setting our id column as SERIAL with PRIMARY KEY attached, Postgres will handle all the complicated behind-the-scenes work and automatically increment our id column with a unique, primary key value for every INSERT .
You must own the sequence to use ALTER SEQUENCE . To change a sequence's schema, you must also have CREATE privilege on the new schema. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the sequence's schema.
In PostgreSQL, the GENERATED AS IDENTITY constraint is used to create a PostgreSQL identity column. It allows users to automatically assign a unique value to a column. The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the PostgreSQL's SERIAL column. Let's analyze the above syntax.
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.
66
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
