In this tutorial, we will learn how to create a table in PostgreSQL. Also, we will demonstrate the step-by-step guide to create a table using SQL Shell (psql) and also by using pgAdmin in PostgreSQL. By the end of this tutorial, you will have complete knowledge of creating a table in PostgreSQL.
- How to create a table in PostgreSQL
- How to create a table in PostgreSQL using Terminal
- How to create a table in PostgreSQL using pgAdmin
- How to create a table in PostgreSQL with Primary Key autoincrement
- Create a table in PostgreSQL with Foreign Key
- PostgreSQL create table if not exists
- PostgreSQL create a table like another table
- PostgreSQL create a table in a specific database
How to create a table in PostgreSQL
In this section, we will learn about the fundamentals of creating a Table in PostgreSQL. The CREATE TABLE statement is used to create a new table in PostgreSQL.
Here is the syntax for the CREATE TABLE statement in PostgreSQL to create a table under a database:
CREATE TABLE [IF NOT EXISTS] TABLE_NAME(
COLUMN_NAME_1 DATATYPE CONSTRAINT,
COLUMN_NAME_2 DATATYPE CONSTRAINT,
COLUMN_NAME_3 DATATYPE CONSTRAINT,
PRIMARY KEY(NAME OF COLUMN OR COLMUNS)
);
- Type the name of the table to be created after the CREATE TABLE keywords.
- It is optional to type IF NOT EXISTS as it does not throw an error if a table with the same name already exists rather only notice is issued.
- Then we specify a list of columns, separated by commas in the following order:
- Specify the name of the column to be created in the new table.
- Specify the data type of the column.
- In the end, specify the constraints for the specific column such as NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY. Read about these constraints in the table below.
CONSTRAINT | DESCRIPTION |
---|---|
NOT NULL | It is used to ensure that the values in a column cannot be a NULL value. |
UNIQUE | It is used to ensure that the values should be unique across all the rows in a table. |
CHECK | It makes sure that the value in this column should necessarily meet a specific requirement. |
PRIMARY KEY | It is a column or a group of columns used to identify a row uniquely in a table. This constraint allows us to define a particular column as the Primary Key. |
FOREIGN KEY | It is a column or a group of columns used to identify a row uniquely of a different table. It allows us to specify Foreign Keys for the table. |
Read: PostgreSQL installation on Linux step by step
How to create table in PostgreSQL using Terminal
In this section, we will learn to create a table in PostgreSQL using command line or Terminal. The steps to create a table using the terminal, i.e., SQL Shell(psql) are as follows:
- Open the SQL Shell(psql) which is a terminal based front-end to PostgreSQL.

- Connect to the database using the
\c
command followed by the database name. - In our case, sqlserverguides is the name of the database.
\c sqlserverguides

- The next step in the process is to create a table in PostgreSQL.
- CREATE TABLE statement is used to create a table in PostgreSQL.
- This statement requires the name of the table, name of the columns with the data type and constraints (optional).
- Here is the example of creating a table in PostgreSQL using CREATE TABLE statment.
CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Experience integer,
Salary money);
This is the image of the execution of the above code snippet in PostgreSQL. In this Picture, you can notice that in the second last line there is a message ‘CREATE TABLE’ which means the table is successfully created.

- In the above section, we have learnt how to create a table in PostgreSQL using CREATE TABLE statement using the command line or Terminal.
- In this section, we will see how to see the tables created inside the selected database.
- To see the List of Relations or tables, use
\d
command. - Here is the code snippet showing the list of tables in the selected database. In our case, the database name is sqlserverguides.
\d
Here is the output of the above code. There is one table inside the database ‘sqlserverguides’ and the name of the table is ’employee’.

- To describe the table just type \d and the name of the table
\d Employee
This gives a detailed description of the table, such as its Name, Type and also the details about the Primary key and other constraints.

This is how to create a table in PostgreSQL using terminal.
Read: How to connect to PostgreSQL database
How to create table in PostgreSQL using pgAdmin
In this section we will learn to Create a table in PostgreSQL using pgAdmin. pgAdmin is a Graphic User Interface used to create, update and delete databases and tables in PostgreSQL. The steps to create a table using pgAdmin in PostgreSQL are as follows:
- Open the pgAdmin4 Application which is a management tool for PostgreSQL. This is the first view after opening the pgAdmin4 application.

- Then we will left click on the Database section and select the required database, in this case the name of database is sqlserverguides.

- Now left click on the database and then select the Schemas section using the left mouse button. In this case we left click on sqlserverguides.

- Now right click on the public section to select the Create option from the drop down menu and then select the Table option.

- Create-Table window appears on the screen.
- Here under the General tab, type the Name of the table.
- We will create a table named Employee.

- Then select the Columns tab, and click the + sign to add columns.
- Type the Name of the column.
- Select the Data type from the given drop down menu or type it manually.
- Toggle the Not NULL button to set your preference and similarly with the Primary Key button.

- We have created a table Employee with Column names Emp_ID, Name, Dept, Experience and Salary with Emp_ID as the Primary key.
- You can also provide Constraints like Unique, Check and Foreign Key under the Constraints Tab.
- Click the Save button to save the table.

In this section, we learned, how to create a table in PostgreSQL using pgAdmin.
Read How to create database in PostgreSQL
How to create a table in PostgreSQL with Primary Key auto-increment
In this section, we will learn how to create a table in PostgreSQL with Primary Key auto-increment. Usually, when we create a table we have added values to the table. But with the help of the auto-increment feature, the value of the primary key can be incremented automatically.
As we know the primary key in a column or a group of columns used to identify a row uniquely in PostgreSQL, so it is considered to be a good practice to add a primary key in a table.
In PostgreSQL, we have a special type of database object generator known as SERIAL. It is used to generate a sequence of integers.
So we can use SERIAL while creating a table in PostgreSQL to make the primary key auto-increment.
The Syntax to create table in PostgreSQL with Primary Key auto-increment is:
CREATE TABLE TABLE_NAME (
COLUMN_NAME SERIAL PRIMARY KEY;
COLUMN_NAME DATATYPE CONSTRAINT;
);
Here is the example of creating a table in PostgreSQL with auto-increment.
CREATE TABLE Employee(
Emp_ID SERIAL NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Experience integer,
Salary money);

Now as we will add values in the table the value of the primary key will be autoincremented.
In this section, we learned how to create a table in PostgreSQL with auto-increment with the help of a pseudo data type named SERIAL.
This is an example of PostgreSQL create table auto_increment primary key.
Create a table in PostgreSQL with Foreign Key
In this section we will learn how to Create a table in PostgreSQL with a Foreign Key. This method will be used when we have to create two tables which has a relationship of parent-child tables.
Foreign Key is a column or a group of columns used to uniquely identify a row of the parent table. The values of a Foreign key of the child table are dependent on the Values of the Primary key of the parent table. Foreign key is also known as referencing key.
The table in which we define Foreign key is known as child table or referencing table. And the table referenced with this Foreign key is known as parent or referenced table.
The syntax to create a table in PostgreSQL with Foreign Key is:
[CONSTRAINT CONSTRAINT_NAME]
FOREIGN KEY [FOREIGNKEY_NAME]
REFERENCES [PARENT_TABLENAME]
Here is the example of creating a table in PostgreSQL with a Foreign Key.
CREATE TABLE Department(
Dept_ID integer NOT NULL PRIMARY KEY,
Emp_ID integer,
Dept_Name text NOT NULL,
CONSTRAINT fk_Employee
FOREIGN KEY (Emp_ID)
REFERENCES Employee(Emp_ID)
);

When we Describe the table we can see in the description that it clearly mentions about the Foreign Key Constraints, as follows:

In this section we learnt how to create a table in PostgreSQL with a Foreign Key.
PostgreSQL create table if not exists
In this section, we will learn to create a table in PostgreSQL using the IF NOT EXISTS feature. As the name suggests, we use this statement when we want to create a table, if a table with the same name does not exist in the database.
So when we use this statement it checks the entire database before creating the new table for a table with the same name, if it finds a table having the same name as that of the new table it will generate a Notice and the process continues. If we do not use this statement then an error is generated in such a situation which breaks down the entire process.
So the syntax to create a table using IF NOT EXISTS in PostgreSQL is:
CREATE TABLE [IF NOT EXISTS] TABLE_NAME(
COLUMN_NAME_1 DATATYPE CONSTRAINT,
COLUMN_NAME_2 DATATYPE CONSTRAINT,
COLUMN_NAME_3 DATATYPE CONSTRAINT,
PRIMARY KEY(NAME OF COLUMN OR COLMUNS)
);
It is clear from the syntax that we have to just use the IF NOT EXISTS keywords after the CREATE TABLE command and the system will understand we want to have a check before creating the new table.
For the implementation, first we will create a new table as follows:
CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Experience integer,
Salary money);

Now we will try to make a table with the same name and see the results.
CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Experience integer,
Salary money);

It is quite clear from the screenshot that when we do not use IF NOT EXISTS the system generates an error: relation ’employee’ already exists.
Now we will make a table using IF NOT EXISTS statement:
CREATE TABLE IF NOT EXISTS Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Experience integer,
Salary money);

It is clear from the screenshot that the system generates a notice rather than an error and the process continues without any error as this part of the code gets skip in the system.
In this section we learnt how to create a table using IF NOT EXISTS in PostgreSQL.
PostgreSQL create table like another table
In this section, we will learn to create a table like another existing table in PostgreSQL.
So when we create a table like another table then we can either copy the whole table structure and data of an existing table or we can copy the table structure and some partial data or no data at all.
If we need to copy the whole table structure along with the data then we use this syntax:
CREATE TABLE New_Table
AS
TABLE Existing_Table;
If we need to copy the table structure without the data then we use this syntax:
CREATE TABLE New_Table
AS
TABLE Existing_Table
WITH NO DATA;
If we need to copy the table structure but only some partial data then we will use:
CREATE TABLE New_Table
AS
TABLE Existing_Table
SELECT *
FROM
EXISTING_TABLE
WHERE
EXPRESSION/CONDITION;
For the Implementation first we will create a new table in PostgreSQL as follows:
CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Experience integer,
Salary money);

Now add some data in the table using the INSERT INTO statement as follows:
INSERT INTO Employee VALUES(1,'John','IT',4,2500);
INSERT INTO Employee VALUES(2,'Paula','Electrical',2,2000);
INSERT INTO Employee VALUES(3,'Mike','Mechanical',3,2250);
INSERT INTO Employee VALUES(4,'Ross','IT',5,3000);
INSERT INTO Employee VALUES(5,'Henry','Electrical',3,2700);

We can check the data in our table with the help of SELECT command:
SELECT * FROM Employee;

Now we will create a table similar to an existing table in PostgreSQL having the same table structure and the data:
CREATE TABLE Employee_Backup
AS
TABLE Employee;

We can see that the new table has been created and the table structure and data have been completely copied into the new table.
Now we will create a table similar to the existing table in PostgreSQL but without copying the data with the help of this statement:
CREATE TABLE Employee_Backup
AS
TABLE Employee
WITH NO DATA;

Here it is clearly visible that we have created a table similar to an existing table in PostgreSQL without copying the data.
Now we will create a table similar to an existing table in PostgreSQL but this time we will copy some of the data into the newly created table from the existing table.
CREATE TABLE Employee_Backup
AS
SELECT *
FROM
Employee
WHERE
Dept='IT';

Here we have created a similar table to an existing table in PostgreSQL but copied only some of the data as per our requirement.
Alternatively we have one more method which can be used to create a table similar to an Existing table and we can also add some columns of our own. The practical implementation of this code is:
CREATE TABLE Employee_Backup(
like Employee INCLUDING all,
Age integer
);

We have created a table similar to an existing table with some columns of our own. In this section, we learned how to create a table similar to an existing table in PostgreSQL.
PostgreSQL create table in specific database
In this section, we will learn to create a table in a specific database in PostgreSQL. We know that there are a lot of databases in a dataset and we have to be very specific about the database when we are creating tables in PostgreSQL.
First of all List down all the available databases with the help of \l
command.
\l

Now we can connect to the specific database that we want to work upon. By using the \c
command along with the name of the database we can connect to the specific database.
\c sqlserverguides

After using \c
command to the control shifts to the requested database and now we can proceed to create a table in PostgreSQL.
To create table in PostgreSQL we will use CREATE TABLE Statement as follows:
CREATE TABLE Employee(
Emp_ID integer NOT NULL PRIMARY KEY,
Name text NOT NULL,
Dept text,
Experience integer,
Salary money);
Here is the implementation of the above-mentioned code in SQL Shell (psql). We have demonstrated the table creation under the database sqlserverguides.

You may like:
In this tutorial, we have learned how to create table in PostgreSQL. Also we have covered these topics.
- How to create a table in PostgreSQL using Terminal and pgAdmin
- How to create a table in PostgreSQL with Primary Key autoincrement
- Create a table in PostgreSQL with Foreign Key
- PostgreSQL create table if not exists
- PostgreSQL create table like another table
- PostgreSQL create table in specific database
For a better understanding of the topic watch my YouTube video:
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.