SQLAlchemy Error '(psycopg2.errors.NotNullViolation) null value in column "id" of relation

Question

I am aware of a similar issue How to fix error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint? but the answers there did not fix my error

I have the following sqlalchemy structure connected to a postgres database

class Injury(db.Model):
    __tablename__ = "injury"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    description = Column(String)

The DDL looks like

create table injury
(
    id               bigint not null
        constraint idx_182837_injury_pkey
            primary key
        constraint injury_id_key
            unique,
    name             text,
    description      text,
);

However, upon trying to insert something into the database like the following

injury = Injury(name='name', description='desc')
session.add(injury)
session.commit()

The following error occurs

Error '(psycopg2.errors.NotNullViolation) null value in column "id" of relation "injury" violates not-null constraint

Upon opening up my debugger, I can verify that the id attribute in the injury object before I commit is None

Why is this occurring? Shouldn't primary_key=True in my schema tell postgres that it is responsible for making the id? I tried playing around with SEQUENCE and IDENTITY but I was getting the same error

Answer 1

you have to tell it to auto increment dont use bigint but serial

id SERIAL PRIMARY KEY

check this how-to-define-an-auto-increment-primary-key-in-postgresql-using-python

Answer 2

This was a result of some form of bug during my transfer from sqlite to postgres when I used pg_loader. I found out someone else encountered this and I followed their instructions and it corrected my issue. The following steps were all done in IntelliJ's database tool

• Exported all of my data out with pg_dump
• Reformatted my id schema to look like id = Column(Integer, Identity(start=2116), primary_key=True) where 2116 is one more than the last id i currently have in my database
• Reloaded data base in with pg_loader

This was working without the Identity field but the key was set to 1 instead of 2116. Looking at this article helped me realize I needed to use the Identity field.