Python PostgreSQL - Create Table

---

You can create a new table in a database in PostgreSQL using the CREATE TABLE statement. While executing this you need to specify the name of the table, column names and their data types.

Syntax

Following is the syntax of the CREATE TABLE statement in PostgreSQL.

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
);

Example

Following example creates a table with name CRICKETERS in PostgreSQL.

postgres=# CREATE TABLE CRICKETERS (
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Age INT,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255)
);
CREATE TABLE
postgres=#

You can get the list of tables in a database in PostgreSQL using the \dt command. After creating a table, if you can verify the list of tables you can observe the newly created table in it as follows −

postgres=# \dt
            List of relations
Schema  | Name       | Type  | Owner
--------+------------+-------+----------
public  | cricketers | table | postgres
(1 row)
postgres=#

In the same way, you can get the description of the created table using \d as shown below −

postgres=# \d cricketers
               Table "public.cricketers"
Column          | Type                   | Collation | Nullable | Default
----------------+------------------------+-----------+----------+---------
first_name      | character varying(255) |           |          |
last_name       | character varying(255) |           |          |
age             | integer                |           |          |
place_of_birth  | character varying(255) |           |          |
country         | character varying(255) |           |          |
postgres=#

Creating a table using python

To create a table using python you need to execute the CREATE TABLE statement using the execute() method of the Cursor of pyscopg2.

Example

The following Python example creates a table with name employee.

import psycopg2

#Establishing the connection
conn = psycopg2.connect(
   database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)
#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")

#Creating table as per requirement
sql ='''CREATE TABLE EMPLOYEE(
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
)'''
cursor.execute(sql)
print("Table created successfully........")
conn.commit()
#Closing the connection
conn.close()

Output

Table created successfully........