PostgreSQL CREATE TABLE

In this tutorial, we will learn about how to use the PostgreSQL CREATE TABLE statement to create a new table into the Postgres database. Postgres provides us a statement called CREATE TABLE which capable of creating a new table inside the Postgres Database.

PostgreSQL CREATE TABLE Syntax

To create a new table in PostgreSQL you have to follow the syntax.

Syntax

CREATE TABLE [IF NOT EXISTS] table_name 
(
column1 datatype,
column2 datatype,
column3 datatype,
...
...
...
...
...
columnN datatype,
PRIMARY KEY( one or more columns )
);
 

Break down the above code

• First, specify the name of the table after CREATE TABLE statement.
• IF NOT EXISTS option allow you to only create the new table if it does not exist other it will raise an error.
• In above PostgreSQL CREATE TABLE STATEMENT table_name represent the name of the table which you want to create.
• column1 represent the name of the column which you want to create.
• datatype represent of data type of column should be.
• Specify all the column seperated by commas.
• Finally specify the constrains of the table using Foreign key, Unique Key, Primary Key and check, etc.

Now it’s time to come to create a new table in PostgreSQL with the help of the example.

PostgreSQL Create Table Example

Here we are about to see a total of two ways to create a table in Postgres first is using PSQL shell and the second is using the pgAdmin4 GUI application.

Here we are going to create a table named “Students” with fields st_id, first_name, last_name, an age where st_id will be the Primary key.

Create PostgreSQL Table using psql shell

Login to your psql shell and execute the following statement to create a new table.

Example: Create Table PostgreSQL

CREATE TABLE IF NOT EXISTS Students (
st_id int NOT NULL,
first_name VARCHAR(20),
last_name VARCHAR(20),
age INTEGER,
PRIMARY KEY (st_id)
);

Explain the example above:

• Firstly, we specify the name of the new table after the CREATE TABLE clause.
• The name of the table is Students as you can see.
• We used not null clause in the st_id field, which means st_id can not be null.
• Then, you define all the columns of the Students table with their data types.
• After listing all the columns, we use PRIMARY KEY to make the st_id primary key.

Create PostgreSQL Table using pgAdmin 4 GUI

To create a table in Postgres using pgAdmin4 GUI ( Graphical User Interface ) tool, you have to follow the following steps.

• Open the pgAdmin4 application and log in.
• Select Database from the left panel where all the databases are listed.
• Click on Query Tool .
• Paste/Write above SQL query inside query editor and click on Execute button .
• If everything will be good, Then you will get a successful message.

You can see all the above steps within a single screenshot.

Conclusion

So In this article, we have seen all about how to create a table in Postgres with the help of the PostgreSQL CREATE TABLE statement. Here we have a total of two ways to create a Postgres table first is using SQL shell and the second is using the pgAdmin 4 GUI tool.

I hope this article will help you, If you like this article, please share and keep visiting for PostgreSQL tutorials.

Ref:- Click Here

Thanks for your valuable time 👏👏👏 …