Set SQLAlchemy to use PostgreSQL SERIAL for identity generation

Question

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),
    ...
);

to

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.

Please *always* give your PostgreSQL version in questions, along with other appropriate details like Python and psycopg2 versions. — Craig Ringer, Jul 18 '13 at 01:50
Including your versions helps people when they find your question later, and can sometimes help us answer a question better, so thankyou. All pretty civilized in this case, you're not running anything ancient or any weird version combos. — Craig Ringer, Jul 18 '13 at 06:14
related [question](http://stackoverflow.com/q/244243/1309352) — Bleeding Fingers, Jul 18 '13 at 10:27

Craig Ringer · Answer 1 · 2013-07-18T06:57:02.507

Your PRIMARY KEY should be defined to use a SEQUENCE as a DEFAULT, either via the SERIAL convenience pseudo-type:

CREATE TABLE blah (
    id serial primary key,
    ...
);

or an explicit SEQUENCE:

CREATE SEQUENCE blah_id_seq;

CREATE TABLE blah (
    id integer primary key default nextval('blah_id_seq'),
    ...
);

ALTER SEQUENCE blah_id_seq OWNED BY blah.id;

This is discussed in the SQLAlchemy documentation.

You can add this to an existing table:

CREATE SEQUENCE blah_id_seq OWNED BY blah.id;

ALTER TABLE blah ALTER COLUMN id SET DEFAULT nextval('blah_id_seq');

if you prefer to restore a dump then add sequences manually.

If there's existing data you've loaded directly into the tables with COPY or similar, you need to set the sequence starting point:

SELECT setval('blah_id_seq', max(id)+1) FROM blah;

I'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.

What 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.

One 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.

setting the echo of the engine I found the `CREATE TABLE` statements generated by SQLAlchemy and that of the `.dump`, exactly the same, except for the `SERIAL` clause. `DEFERRABLE` had to be added in order to make `SET CONSTRAINTS ALL DEFERRED;` functional. So I guess both methods of record migration would have the same result. — Bleeding Fingers, Jul 18 '13 at 06:54
@hus787 Edited answer and added a note pointing out how to add `SEQUENCE`s afterwards, in case you want to do that. The two methods are *not* equivalent, precisely *because* SQLAlchemy is generating a `SERIAL` pseudo-column. You *need* that, or need to create the equivalent sequence manually. — Craig Ringer, Jul 18 '13 at 06:58

score 0 · Answer 2 · edited Jul 18 '13 at 01:49

0

Use

alter sequence foo_id_seq restart with 7600

should give you 7601 next time you call the sequence.

http://www.postgresql.org/docs/current/static/sql-altersequence.html

And then subsequent values. Just make sure that you restart it with a value > the last id.

edited Jul 18 '13 at 01:49

Craig Ringer

307,061
76
688
778

answered Jul 17 '13 at 11:28

MortenSickel

2,118
4
26
44

seems like a temporary solution to me. Because next time an `INSERT` is done with value of `id`(= sequence's current value + 1) explicitly supplied, the sequence will give a non-unique values for the next subsequent `INSERT`. – Bleeding Fingers Jul 17 '13 at 12:27
Ah, so you are inserting explicit ID values. Then I would say "serial" makes no sense and your id should just be a normal integer – MortenSickel Jul 17 '13 at 12:34
secondly, I have 25 sequences altogether, so setting each one of them looks painful – Bleeding Fingers Jul 17 '13 at 12:34
actually explicit `id` values were supplied during the migration. And apart from that there can rise a case in future where the an `id` will be supplied explicitly, so I don't want the application to get buggy just because of some of those cases. – Bleeding Fingers Jul 17 '13 at 12:38
So decide what you want to do. Have non-locking auto-generated ids or manually enter your own values. It's logically impossible to do both, isn't it? – Richard Huxton Jul 17 '13 at 14:19
@RichardHuxton one question: then how does SQLite3 manages to do it both ways? – Bleeding Fingers Jul 17 '13 at 17:26
It doesn't. It's impossible to do so. Without checking, it almost certainly locks the table while it hands out the "next" number. You can write code in PostgreSQL to do that if you want, but it's usually a bad idea. How does the client code know the ID number it's providing isn't being provided by another client at the same time? – Richard Huxton Jul 17 '13 at 18:42
@RichardHuxton Those are the internals I don't won't to worry about at the moment, just hand out the number. And yeah that's the code I am basically looking for, preferably with the sugar. Those clashes could be really rare in the application, although right now the client side code doesn't do any such thing. And please use the "@" sign. – Bleeding Fingers Jul 17 '13 at 20:50

Set SQLAlchemy to use PostgreSQL SERIAL for identity generation

2 Answers2

Linked