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.
Headings of Contents
PostgreSQL CREATE TABLE Syntax
To create a new table in PostgreSQL you have to follow the 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 EXISTSoption 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_namerepresent 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
- The name of the table is Students as you can see.
- We used
not nullclause in the
st_idfield, which means
st_idcan not be null.
- Then, you define all the columns of the
Studentstable with their data types.
- After listing all the columns, we use PRIMARY KEY to make the
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.
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 👏👏👏 …