How to Define an Auto Increment Primary Key in PostgreSQL using Python?
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.
Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.
To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning - Basic Level Course
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 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>(
<column1_name> SERIAL NOT NULL PRIMARY KEY,
The implementation of creating a table with such specification is given below:
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.
Using python code:
Output of employee table after executing above program :