Set SQLAlchemy to use PostgreSQL SERIAL for identity generation

Ask Time：2013-07-17T19:23:55 Author：Bleeding Fingers

Background:

The application I am currently developing is in transition from SQLite3 to PostgreSQL. All the data has been successfully migrated, using the .dump from the current database, changing all the tables of the type

CREATE TABLE foo (
    id INTEGER NOT NULL, 
    bar INTEGER,
    ...
    PRIMARY KEY (id),
    FOREIGN KEY(bar) REFERENCES foobar (id),
    ...
);

CREATE TABLE foo (
    id SERIAL NOT NULL, 
    bar INTEGER,
    ...
    PRIMARY KEY (id),
    FOREIGN KEY(bar) REFERENCES foobar (id) DEFERRABLE,
    ...
);

and SET CONSTRAINTS ALL DEFERRED;.

Since I am using SQLAlchemy I was expecting things to work smoothly from then on, after of course changing the engine. But the problem seems to be with the autoincrement of the primary key to a unique value on INSERT.

The table, say foo, I am currently having trouble with has 7500+ rows but the sequence foo_id_seq's current value is set on 5(because I have tried the inserts five times now all of which have failed).

Question:

So now my question is that without explicitly supplying the id, in the INSERT statement, how can I make Postgres automatically assign a unique value to the id field if foo? Or more specifically, have the sequence return a unique value for it?

Sugar:

Achieve all that through the SQLAlchemy interface.

Environment details:

Python 2.6
SQLAlchemy 8.2
PostgreSQL 9.2
psycopg2 - 2.5.1 (dt dec pq3 ext)

PS: If anybody finds a more appropriate title for this question please edit it.

Author:Bleeding Fingers，eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/17698521/set-sqlalchemy-to-use-postgresql-serial-for-identity-generation

Craig Ringer :

Your PRIMARY KEY should be defined to use a SEQUENCE as a DEFAULT, either via the SERIAL convenience pseudo-type:\n\nCREATE TABLE blah (\n id serial primary key,\n ...\n);\n\n\nor an explicit SEQUENCE:\n\nCREATE SEQUENCE blah_id_seq;\n\nCREATE TABLE blah (\n id integer primary key default nextval('blah_id_seq'),\n ...\n);\n\nALTER SEQUENCE blah_id_seq OWNED BY blah.id;\n\n\nThis is discussed in the SQLAlchemy documentation.\n\nYou can add this to an existing table:\n\nCREATE SEQUENCE blah_id_seq OWNED BY blah.id;\n\nALTER TABLE blah ALTER COLUMN id SET DEFAULT nextval('blah_id_seq');\n\n\nif you prefer to restore a dump then add sequences manually.\n\nIf there's existing data you've loaded directly into the tables with COPY or similar, you need to set the sequence starting point:\n\nSELECT setval('blah_id_seq', max(id)+1) FROM blah;\n\n\nI'd say the issue is likely to be to do with your developing in SQLite, then doing a dump and restoring that dump to PostgreSQL. SQLAlchemy expects to create the schema its self with the appropriate defaults and sequences.\n\nWhat I recommend you do instead is to get SQLAlchemy to create a new, empty database. Dump the data for each table from the SQLite DB to CSV, then COPY that data into the PostgreSQL tables. Finally, update the sequences with setval so they generate the appropriate values.\n\nOne way or the other, you will need to make sure that the appropriate sequences are created. You can do it by SERIAL pseudo-column types, or by manual SEQUENCE creation and DEFAULT setting, but you must do it. Otherwise there's no way to assign a generated ID to the table in an efficient, concurrency-safe way.",

2013-07-18T01:44:16

Set SQLAlchemy to use PostgreSQL SERIAL for identity generation