EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL INSERT INTO
 

PostgreSQL INSERT INTO

Updated May 24, 2023

PostgreSQL INSERT INTO

 

 

Introduction to PostgreSQL INSERT INTO

PostgreSQL provides an insertion operation we call as INSERT INTO statement. Insert into statement performs essential functions in the database administration system. With the help of insert into statements, we can insert single records or multiple records into database tables at a time. Before the insertion operation, we need a table, without the creation of the table, we cannot perform insert into statement. Insert into comes under the data manipulation category. The set of manipulation commands is used to manipulate data stored in databases. Under insert into the statement, we can perform different operations on the table.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax:

INSERT       INTO   SPECIFIED     TABLE NAME   (COLUMN NAME 1,    COLUMN NAME 2,         COLUMN NAME 3 .............              COLUMN NAME N)       VALUES      (VALUE 1,        VALUE 2,         VALUE 3, .................. VALUE N);

Explanation:

  • In the above syntax where insert into, values are keywords, table name means specified table name that we need to insert data after column name means specified column name in the table in which we need to insert data, and value means an actual value that we need to insert into the table.
  • Column and values are separated using a comma. If the data is a string, then use a double inverted comma.

How PostgreSQL INSERT INTO Statements work?

  • We must install PostgreSQL in our system.
  • Require basic knowledge about PostgreSQL.
  • We must require tables to perform the insert operation.

Different Methods to insert into the statement are as follows:

Let’s create a table to perform insert into a statement using a create table statement. So we use the following statement to create a table.

Syntax:

create table               table name                ( column name_1      data type             (null | not null),          column name_2          data type ( null | not null),…………….  Column name_N);

Explanation:

  • In the above syntax where create the table is a keyword, table name means new table name that we need to develop, and column names 1 and 2 specified column name in the table with data type and operator.
  • We are separating the name of the columns by using the comma.

Example:

Code:

CREATE TABLE            emp_info       (
emp_id                     INT PRIMARY KEY       NOT NULL,
emp_name               TEXT                           NOT NULL,
emp_age                  INT                             NOT NULL,
address                    CHAR(50),
salary                       REAL);

Explanation:

  • In the above statement, we create a table name as emp_info using create table statement. In the emp_info table, we created 5 columns such as emp_id, emo_name, emp_age, emp_address, and salary with different data types and also we defined emp_id as the primary key with not null constraint, and we use real data type to salary column because salary may be a float.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL INSERT INTO 1

Now we perform insert into a statement on the table using the following methods.

Basically, we use two methods for insertion operation as follows:

1. Single row insert into operation.

In a single row insertion operation, we can insert a single row at a time in the table.

Syntax:

Inset    into    table name                (column 1, column 2, …………………column n ) values                    (value 1, value 2, …………….value n);

Explanation:

  • In the above syntax, where insert into is a keyword, where the table name is the specified table name, column 1, column 2, column n column name in the table, and values means that we need to insert.

Example:

Code:

INSERT INTO               emp_info                    (emp_id,         emp_name,             emp_age,                   address,                      salary)
VALUES           (1,        'poll',  28,      'london',        30000.00);

Explanation:

  • In the above statement table name is emp_info that table we already created with the above columns, so we need to add values in the table.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Single row

2. Multiple row insertion operations.

In this method, we insert multiple rows at a time using the following syntax.

Syntax:

Code:

Inset    into    table name                (column 1, column 2, …………………column n ) values                    (value 1,         value 1, …………….value n),
(value 2,          value 2, …………….value n),
(value 3,          value 3, …………….value n);

Explanation:

  • Suppose we need to insert multiple rows at a time, that time, we use the above syntax similarly where to insert into is a keyword, the table name is the specified table name, column 1 is a column name in the table values that we need to insert.
  • Column name and values are separated by using a comma.

Example:

Code:

INSERT INTO                     emp_info                           (emp_id,             emp_name,               emp_age, address,         salary)
VALUES               (2,          'alex',   30,        'Hongkong',      40000.00),
(3,          'john',  35,        'Newyork',          50000.00),
(4,          'bob',    32,        'Sydney',            20000.00);

Explanation:

  • In the above example, we have emp_info table, emp_id, emp_name, e,p_age, address, and salary, which are the column names in which we need to insert data and values to insert value in the column.
  • If the data is a string, then use a double inverted comma.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

Multiple row

Another way to perform insert into a statement is by using the following statement.

Example:

Code:

INSERT INTO                     emp_info      VALUES      (8,         'paul',  30,        'perth',        40000.00);

Explanation:

  • In the above statement, we insert values directly into the table without the column name.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL INSERT INTO 4

Getting the last insert row in the table.

We use the following statement to get the last inserted row in the table.

Code:

insert                   into                       college                (branch,             no_of_student)               values ('civil',                   60)        returning           branch;

Explanation:

  • In the above statement, we created a table name as college and a two-column branch and no of students, and we insert value civil in-branch column and 60 in no of students, and the returning branch is used to know the last inserted branch in the table result of returning clause is similar with select clause.
  • Illustrate the end result of the above declaration by using the use of the following snapshot.

Output:

PostgreSQL INSERT INTO 5

Output Notation Message:

  • Insert oid 1: The meaning of the above message is it returns only one row.
  • Insert 0 #: The meaning of the above message is it returns multiple rows. # is used to show the number of rows inserted.

Conclusion

From the above article, we saw the basic syntax of the insert into statement then we also saw how we can use insert into a statement by using different methods like single row and multiple rows with examples. From this article, we saw how we can handle insert into statements correctly.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL INSERT INTO” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. Alter Column in PostgreSQL
  2. PostgreSQL DROP DATABASE
  3. DDL in PostgreSQL
  4. PostgreSQL IS NULL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW