PostgreSQL basic: Create a table and Insert Data

PostgreSQL is one of the best databases out there and today we’re come up with this awesome article. In this article, I’m gonna guide you on how you can create a table in the Postgres database. And will also show you how you can insert data into those tables.

Basically, we’re going to perform all these operations with SQL queries. So, just open your PgAdmin and then follow this guide.

How to create a table in PostgreSQL?

Here is our basic structure to create a table in Postgres:

CREATE TABLE table_name (
    column_name_1 DATA_TYPE CONSTRAINTS,
    column_name_1 DATA_TYPE CONSTRAINTS,
    column_name_1 DATA_TYPE CONSTRAINTS,
    ...
    ...
    column_name_n DATA_TYPE CONSTRAINTS
);

In the above format, you can see that we start with CREATE TABLE keyword followed by table_name. And then inside the (); you can define your columns.

To define column, first you need to enter column name followed by DATA TYPE and then followed by constraints(if any).

• Column name would be like first_name, age, etc, which will define column
• DATATYPE will depend on the data you want to keep in that column, e.g. SERIAL, INTEGER, VARCHAR, CHAR, etc.
• CONSTRAINTS will be condition e.g. NOT NULL, PRIMARY KEY, etc.

Now for instance, let’s create a table for example. Our table name will be students and we will have five different column with different type of constraints, e.g. roll_no, full_name, date_of_birth, phone_no., address.

So, to create a table, go to your database then right-click on it, and then click on create the script. Then this will open a new window inside your dashboard, where you can write your PostgreSQL queries. write the following code and select it before execution. then hit run button or (fn+f5) or f5

Just run this above script inside your query editor and then your students table will be successfully created.

You can view your students table using following script:

SELECT * FROM students;

* means we want every column and students is table_name.

Our table showing no data, because haven’t entered one yet. Now that’s what we’re going to learn next. I’m going to show how you can enter data into your students table.

How to insert data into a Postgres table?

Now let’s explore how you can insert data into the table. Basically, we’re going to insert data inside the students table, that we just created. But before that, let me explain to you the syntax to insert data:

INSERT INTO table_name(column_name1, column_name2, column_name3) VALUES (data_1, data_2, data_3);

Now in above syntax “INSERT INTO” and “VALUES” are reserved keyword. then you need to enter column names inside brackets, in which you want to enter the data. Now let me show you with an example.

INSERT INTO students(full_name,date_of_birth,phone_no,address)
 VALUES ('PK Karn','1999-12-14',34234,'Delhi 192922');

Run this above queries and then data will be successfully inserted inside your table. Now in the above script, you don’t need to enter roll_no separately because we declared it’s data type as SERIAL, which means it will increment itself by 1 on each new data insertion.

You can also enter multiple data at one time, all you need is to separate it with ‘,’.

INSERT INTO students(full_name,date_of_birth,phone_no,address)
 VALUES
 ('Ritesh','1999-01-12',245354,'Banglore 110033'),
 ('Sohail','1995-04-03',123423,'UP 110032'),
 ('Rohit','2000-02-02',23899,'New Delhi 780033');

Now you run the above script and then see your table with select query: SELECT * FROM students;

So, Now you finally learned how to create a table and insert data into PostgreSQL Database.

Read Also: How to create database in PostgreSQL

I hope you all liked this article. If yes then please don’t forget to share this article with your friends. You can also subscribe to our blog via email to get future notifications from us.

Thanks to read.