id column with serial small integer primary key
Issue #3019
invalid
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?
Comments (3)
-
repo owner -
repo owner - changed status to invalid
postgresql has no SMALLSERIAL datatype, so explicit APIs must be used.
-
reporter Good to know, I guess the difference between using integer or small integer is academic anyway.
- Log in to comment
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:
docs: http://docs.sqlalchemy.org/en/latest/core/defaults.html?highlight=sequence#defining-sequences