Hi I am trying to develop insert functionality using python and postgress sql. What I am trying to achieve is that when Insert a value, the id should be auto incremented as I have set it that way. But still gives me an error of non null. See error below

This is my current code:

def insert(car_make,car_model,year,first_owner,vinnumber):
    conn=psycopg2.connect("dbname='Car_Inventory_db' user='postgres' password='password' host='localhost' port='5432'")
    cur=conn.cursor()
    cur.execute("INSERT into carInventory(car_make,car_model,year,first_owner,vinnumber) VALUES (%s, %s, %s, %s, %s)", (car_make,car_model,year,first_owner,vinnumber))
    conn.commit()
    conn.close()

Error I am receiving:

psycopg2.errors.NotNullViolation: null value in column "id" of relation "carinventory" violates not-null constraint
DETAIL:  Failing row contains (null, Honda, Civic, 2003, true, 3sdfSKHAN).

This is how I created my table

def connect():
   conn=psycopg2.connect("dbname='Car_Inventory_db' user='postgres' password='password' host='localhost' port='5432'")
   cur=conn.cursor()
   cur.execute("""CREATE TABLE IF NOT EXISTS carInventory(
    id integer PRIMARY KEY,
    car_make text,
    car_model text,
    year integer,
    first_owner boolean,
    vinnumber varchar(17)
)
""")
   conn.commit()
   conn.close()

How do I avoid the error, so my code takes in all the value, and only auto increment the id column based on the insert statement

CodePudding user response：

Use SERAIL data type in your table definition.

This will do the auto-increment for you.

  id SERIAL PRIMARY KEY

See here for more.

CodePudding user response：

As of Postgres v10 the preferred method is to define your id as a generated identity column.

create table carinventory(                                                                                                                         
             id integer  generated always as identity                                                                                              
                         primary key                                                                                                               
           , car_make text                                                                                                                         
           , car_model text                                                                                                                        
           , year integer                                                                                                                          
           , first_owner boolean                                                                                                                   
           , vinnumber varchar(17)                                                                                                                 
           ) ;