id column with serial small integer primary key

[Moritz E. Beber]

It's rather common to declare an id column as primary key which is usually converted effortlessly. (I work with Flask-SQLAlchemy and a postgresql database.)

class Dummy(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(16), unique=True, index=True)

Emits the following statement:

CREATE TABLE dummy (
        id SERIAL NOT NULL, 
        name VARCHAR(16), 
        PRIMARY KEY (id)
)

Perfect! However, when I declare the id column with a small integer:

class Dummy(db.Model):
    id = db.Column(db.SmallInteger, primary_key=True)
    name = db.Column(db.String(16), unique=True, index=True)

The emitted statement is:

CREATE TABLE dummy (
        id SMALLINT NOT NULL, 
        name VARCHAR(16), 
        PRIMARY KEY (id)
)

and adding an object naturally leads to an error:

(IntegrityError) null value in column "id" violates not-null constraint
 'INSERT INTO dummy (name) VALUES (%(name)s) RETURNING dummy.id' {'name': 'crash test'}

Is there a more profound reason than a type check preventing this from being possible?

[Mike Bayer]

absolutely. Postgresql has no SMALLSERIAL type:

http://www.postgresql.org/docs/9.0/static/datatype-numeric.html#DATATYPE-SERIAL

if you'd like to associaite a sequence with a smallint, use Sequence:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(SmallInteger, Sequence('a_seq'), primary_key=True)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)

e.execute(A.__table__.insert())

docs: http://docs.sqlalchemy.org/en/latest/core/defaults.html?highlight=sequence#defining-sequences

[Mike Bayer]

postgresql has no SMALLSERIAL datatype, so explicit APIs must be used.

[Moritz E. Beber]

Good to know, I guess the difference between using integer or small integer is academic anyway.