Create A Table In PostgreSQL Using Python And Psycopg2


  • A database table in PostgreSQL is created using the SQL Statement CREATE TABLE.
  • CREATE TABLE is a one of the essential DDL statements supported by PostgreSQL.
  • A PostgreSQL user, can create a new database in PostgreSQL server and add tables into it or can create tables in an already existing PostgreSQL database.
  • Inside a database, tables are stored under a schema. When a schema name is not specified tables are created inside a default schema named public.
  • To create a table from a Python Program, Psycopg2  - the Python client library for PostgreSQL can be used.

Creating a database table in PostgreSQL using Python and Psycopg2:

  • The implementation of Psycopg2 adheres to the Python Database Access Specification.
  • In a Python Program, using the Psycopg2, a developer can create a database connection, a database cursor and call the necessary execute() and fetch() methods to perform any of the required SQL operations on a PostgreSQL server. 
  • The CREATE TABLE statement is as well, executed through a database cursor object obtained using the Psycopg2 module.
  • The Python example program below, creates a table in PostgreSQL server and prints the list of tables under the default schema, in this case a schema named test,not the standard default schema. 


# import the PostgreSQL adapter for Python

import psycopg2


# Connect to the PostgreSQL database server

postgresConnection    = psycopg2.connect("dbname=test user=test password='test'")


# Get cursor object from the database connection

cursor                = postgresConnection.cursor()


name_Table            = "news_stories"


# Create table statement

sqlCreateTable = "create table "+name_Table+" (id bigint, title varchar(128), summary varchar(256), story text);"


# Create a table in PostgreSQL database




# Get the updated list of tables

sqlGetTableList = "SELECT table_schema,table_name FROM information_schema.tables where table_schema='test' ORDER BY table_schema,table_name ;"

#sqlGetTableList = "\dt"


# Retrieve all the rows from the cursor


tables = cursor.fetchall()


# Print the names of the tables

for table in tables:




List of tables under current schema

('test', 'news_stories')

('test', 'news_stories_archive')


Copyright 2020 ©