Menu Close

PostgreSQL CREATE TABLE

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 Postgres Create Table.
  • Paste/Write above SQL query inside query editor and click on Execute button Postgres Create Table.
  • If everything will be good, Then you will get a successful message.

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

Postgres Create Table

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 👏👏👏 …

Previous Article
Next Article

Related Posts