How to create a table in PostgreSQL [Terminal + pgAdmin]

by
PostgreSQL Table Name pgAdmin

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.
CONSTRAINTDESCRIPTION
NOT NULLIt is used to ensure that the values in a column cannot be a NULL value.
UNIQUEIt is used to ensure that the values should be unique across all the rows in a table.
CHECKIt makes sure that the value in this column should necessarily meet a specific requirement.
PRIMARY KEYIt 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 KEYIt 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.
Constraints in PostgreSQL

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.
create table in PostgreSQL
create table in 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
create table in PostgreSQL using Terminal
create table in PostgreSQL using Terminal
  • 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.

How to create table in PostgreSQL using Terminal
CREATE TABLE Statement in PostgreSQL
  • 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’.

PostgreSQL create a table
PostgreSQL create a table
  • 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.

PostgreSQL create a table using terminal
PostgreSQL create a table using terminal

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.
create table in PostgreSQL using pgAdmin
PostgreSQL- pgAdmin4 Window
  • Then we will left click on the Database section and select the required database, in this case the name of database is sqlserverguides.
PostgreSQLcreate table using pgAdmin
PostgreSQL- Database Selection
  • 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.
How to create table in PostgreSQL using pgAdmin
PostgreSQL- Selecting Schemas
  • Now right click on the public section to select the Create option from the drop down menu and then select the Table option.
PostgreSQL Create Table pgAdmin
PostgreSQL- Create Table
  • 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.
PostgreSQL create table
PostgreSQL create table
  • 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.
PostgreSQL create table using pgAdmin
PostgreSQL- Column Definition
  • 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.
PostgreSQL Save Table pgAdmin
PostgreSQL- Save Table pgAdmin

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);
PostgreSQL create table auto_increment primary key
PostgreSQL create table auto_increment primary key

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)
);
Create table in PostgreSQL with Foreign Key
PostgreSQL- Foreign Key Definition

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

Create table in PostgreSQL with Foreign Key
PostgreSQL- Foreign Key Description

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);
PostgreSQL create table if not exists
PostgreSQL- Create New Table

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);
PostgreSQL create table if not exists
PostgreSQL create table if not exists

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);
PostgreSQL create table if not exists
PostgreSQL- Notice for the New Table

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);
PostgreSQL Create New Table
PostgreSQL- Create New Table

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);
PostgreSQL create table like another table
PostgreSQL- Insert Data in Table

We can check the data in our table with the help of SELECT command:

SELECT * FROM Employee;
How to create table like another table PostgreSQL
PostgreSQL- Data in Table

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;
PostgreSQL Copy Table With Data
PostgreSQL- Copy Table With Data

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;
PostgreSQL Copy Table Without Data
PostgreSQL- Copy Table Without 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';
PostgreSQL Copy Table With Partial Data
PostgreSQL- Copy Table With Partial Data

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
);
PostgreSQL Create Table Similar To Table
PostgreSQL- Create Table Similar To a Table

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
PostgreSQL create table in specific database
PostgreSQL- List of Databases

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
PostgreSQL create table in specific database
PostgreSQL- Connection to Database

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.

PostgreSQL CREATE TABLE Statement
PostgreSQL- CREATE TABLE Statement

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: