Open In App

How to Define an Auto Increment Primary Key in PostgreSQL using Python?

Last Updated : 06 Jan, 2023
Like Article

Prerequisite: PostgreSQL

Python has various database drivers for PostgreSQL. Currently, most used version is psycopg2 because it fully implements the Python DB-API 2.0 specification.  The psycopg2 provides many useful features such as client-side and server-side cursors, asynchronous notification and communication, COPY command support, etc.


psycopg2 can be downloaded like any other module using the following command:

pip install psycopg2


PostgreSQL’s way of creating Primary key with auto increment feature :

A column has to be defined with SERIAL PRIMARY KEY. Here SERIAL is not a true data type, but is simply shorthand notation that tells Postgres to create an auto incremented, unique identifier for the specified column. By simply setting a column as SERIAL with PRIMARY KEY attached, Postgres will handle all the complicated behind-the-scenes work and automatically increment our the specified column with a unique, primary key value for every INSERT.

Database Information

Database name: testdb

Table name: EMPLOYEE

In the EMPLOYEE TABLE, column named EMPLOYEE_ID will be implemented as an auto-incremented Primary key column. 


CREATE TABLE <table_name>(





The implementation of creating a table with such specification is given below:


import psycopg2
def create_table():
    conn = None
        # connect to the PostgreSQL server
        conn = psycopg2.connect(database="testdb", user="postgres",
                                password="password", host="", port="5432")
        print("Opened database successfully")
        # create a cursor
        cursor = conn.cursor()
        # Dropping EMPLOYEE table if already exists.
        cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
        # Creating table as per requirement, let us have EMPLOYEE table
        # and in order to have auto increment primary key, EMPLOYEE_ID SERIAL PRIMARY KEY
        # is used and it is explained before code
        sql = '''CREATE TABLE EMPLOYEE(
           FIRST_NAME CHAR(20) NOT NULL,
           LAST_NAME CHAR(20),
           AGE INT,
           SEX CHAR(1),
           INCOME FLOAT
        print("Table created successfully........")
        # close communication with the PostgreSQL database server
        # commit the changes
    except (Exception, psycopg2.DatabaseError) as error:
        if conn is not None:
if __name__ == '__main__':

We can see the table created using pgadmin tool

Now, Insertion needs to done to see if our auto-increment feature works or not. This can be done either directly through pgadmin or using python code.

pgadmin way :

Below is the screenshot that shows execution of insert queries and resultant result-set.

Explanation of auto increment primary key

Using python code:


import psycopg2
    connection = psycopg2.connect(user="postgres",
    cursor = connection.cursor()
    # As Employee table is having auto incremented primary id column(employee_id), no need to specify about that value here
    postgres_insert_query = ''' INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE,SEX,INCOME) VALUES (%s,%s,%s,%s,%s)'''
    record_to_insert = ('asd', 'wer', 19, 'f', 5000)
    cursor.execute(postgres_insert_query, record_to_insert)
    count = cursor.rowcount
    print(count, "Record inserted successfully into Employee table")
except (Exception, psycopg2.Error) as error:
        print("Failed to insert record into Employee table", error)
    # closing database connection.
        print("PostgreSQL connection is closed")

Output of employee table after executing above program :

Like Article
Suggest improvement
Share your thoughts in the comments

Similar Reads