Why is Heroku saying that Primary Key is Null?

Question

I just got my flask app to work on heroku, but now the sign up page gives me an error (I tried registering as 'Another User'):

ValueError: null value in column "id" violates not-null constraint DETAIL: Failing row contains (null, Another, User, anotheruser, 2020-12-18, another@user.com, pbkdf2:sha256:150000$Qfubfcls$9d8b1492272dee1c8b200ff34d5a2e73e4..., 0, 0, null).

Here is the CREATE statement for the users table:
```sql
CREATE TABLE 'users' 
(
     'id' integer PRIMARY KEY NOT NULL, 
     'fname' varchar(255), 
     'lname' varchar(255), 
     'username' varchar(255), 
     'dob' date, 
     'email' varchar(255), 
     'password' varchar(255), 
     points INTEGER DEFAULT 0, 
     problems INTEGER DEFAULT 0, 
     bio VARCHAR(200)
)

Based on what I know, a primary key can't be NULL, and I already registered before deploying to Heroku, but I was using SQLite3, and now I'm using PostgreSQL. Login is still functional.

I'm using the CS50 library, and here is the INSERT statement in the register route that causes the error:

db.execute("INSERT INTO users(fname, lname, username, dob, email, password) VALUES (:fname, :lname, :username, :dob, :email, :hash)",
            fname = request.form.get("fname"), lname = request.form.get("lname"),
            username = request.form.get("username"), dob = request.form.get("birth"),
            email = request.form.get("email"), hash = generate_password_hash(request.form.get("pwd")))

Answer 1

Your insert statement doesn't provide a value for id, so it's implicitly null, and thus fails the insert, because a primary key cannot be null. You could explicitly give it a value when inserting, but it could be easier to let the database handle it for you by defining the column as serial.