How to PostgreSQL- Create & Delete Tables

In this tutorial, you will learn how to use CREATE TABLE & DROP TABLE statements to create & delete tables respectively from the database.



The PostgreSQL CREATE TABLE statement is used to create a new, initially empty table in the current database. The table will be owned by the user issuing the command. This PostgreSQL tutorial explains how to use the PostgreSQL CREATE TABLE statement with syntax and examples.

     CREATE TABLE table_name( 
     column1 datatype [ NULL | NOT NULL ],
     column2 datatype [ NULL | NOT NULL ],


Above syntax is the simplest form of CREATE TABLE statement in PostgreSQL. The full syntax for the PostgreSQL CREATE TABLE statement has lots options. Check PostgreSQL documentation here.

PostgreSQL supports the data types: Boolean, Character, Number, Temporal i.e., date and time-related data types, Special types, Array. For more data types, check PostgreSQL documentation here.

CREATE TABLE also automatically creates a data type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema.

     CREATE TABLE payroll(
     firstname VARCHAR(30) NOT NULL,
     lastname VARCHAR(30) NOT NULL,
     payment_date DATE,
     designation VARCHAR(30),
     salary INTEGER NOT NULL


NOT NULL – the value of the column cannot be NULL.
CONSTRAINT – An optional name for a column or table constraint. If the constraint is violated, the constraint name is present in error messages.
UNIQUE – the value of the column must be unique across the whole table
PRIMARY KEY – this constraint is the combination of NOT NULL and UNIQUE constraints. You can define one column as PRIMARY KEY by using column-level constraint.



postgresql create & Delete Tables


You can verify if your table has been created successfully using \d command,


Table List

Create & Delete Tables

Now we have a table named payroll in database lauyou.



PostgreSQL ALTER TABLE statement is used to add a column, modify a column, drop a column, rename a column or rename a table.


To add a new column into the table

     ALTER TABLE table_name
     ADD new_column_name column_definition;


new_column_name: The name of the new column to add to the table.
column_definition: The datatype of the column.

The syntax to modify a column in a table in PostgreSQL is,

     ALTER TABLE table_name
     ALTER COLUMN column_name TYPE column_definition;


column_name: The name of the column to be modified.

The syntax to drop a column in a table in PostgreSQL is,

     ALTER TABLE table_name
     DROP COLUMN column_name;


column_name: The name of the column to delete from the table.

The syntax to rename a table in PostgreSQL is,

     ALTER TABLE table_name
     RENAME TO new_table_name;


table_name: The table to rename.
new_table_name: The new table name.



The PostgreSQL DROP TABLE statement will delete a table from the PostgreSQL database.

     DROP TABLE table_name;
     DROP TABLE payroll;


This above example would delete the table payroll from lauyou database.

Muhsin Yakkiparamban

Muhsin Yakkiparamban is the co-founder of Lauyou Learning. Muhsin holds Bachelor Degree in Civil Engineering from KMCT Calicut.

You may also like...